Replacing a portion of text field

[smmedeiros]

Looking to replace a portion of a text field.
Sample -> Database Oracle ~PATCHING WINDOW,MONTHLY,WEEK2,SATURDAY,23:00-05:00,~
Results desired is to remove the contents between the tilde marks to have new field as "Database Oracle"

Sample2
~PATCHING WINDOW,BIMONTHLY,WEEK4,SUNDAY,11:00-15:00,~ 4yr 24 x 7 Coverage. 4hr onsite responseNetbackup Media Server
desired results "4yr 24 x 7 Coverage. 4hr onsite responseNetbackup Media Server"

I have a formula to extract the data between the tilde marks. I'm unclear how to replace it with 'blanks' to have the desired results simply removing the content.

Appreciate any ideas.

Thanks

 

[BettyJ]

I believe you can make use of the replace function, which has the following syntax:
[tt]Replace (inputString, findString, replaceString)[/tt]
You can use "" as the replacestring to replace with blank.

 

[BettyJ]

Try the following formula:
trim(replace(Replace({Sheet1_.SampleData}, split({Sheet1_.SampleData},"~")[2],""),"~",""));

 

[BettyJ]

In the above post, use your Field instead of Sheet1_.SampleData.

 

[smmedeiros]

Thank you for the suggestion. However, this just seems to pull the values between the tilde ~ marks. i want to remove the contents between ~ ~ and have only the content in bold. Database Oracle ~PATCHING WINDOW,MONTHLY,WEEK2,SATURDAY,23:00-05:00,~ as my resulting data content.

 

[BettyJ]

I am not sure why you are getting the contents between the tilde marks; but if that is the case,then if the name of formula in the above post is formula1,
create another formula
@formula2
[tt]replace({YourField},{@formula1},"");[/tt]
Please post the output you are getting for formula2

 

[pmax9999]

Try the following:

If      UBound(Split({Table.Data},'~')) > 1
Then    Split({Table.Data},'~')[1] 
+
If      UBound(Split({Table.Data},'~')) > 2
Then    Split({Table.Data},'~')[3]

Cheers
Pete

 

[smmedeiros]

Thank You Peter - Worked great! Appreciate all the ideas and help from everyone. Love this Forum!