Extract text before, between and after a character

[greg544]

I cannot figure out how to extract this from all the other examples I find here. (Great site, thanks everyone!)

Text
A1: Bob Smith ~ 123 Main Street ~ Houston ~ Texas ~ 77001


I need:
B1: Bob Smith
C1: 123 Main Street
D1: Houston
E1: Texas
F1: 77001


Thank you!

 

[Joe4]

You can easily parse that out to multiple columns using Text to Columns.
Just highlight the column, select "Text to Columns" from the Data menu, select the Delimited option, and enter the tilde as your delimiter and click Finish.

 

[greg544]

Thanks Joe! Very easy just as you said.

 

[Joe4]

You are welcome!

Yes, Text to Columns is a very handy tool! I use it all the time to split data up into multiple columns.
It works very much like the Import Wizard does, but is applied to only a single column (as opposed to a whole file).

 

[Rick Rothstein]

Thanks Joe! Very easy just as you said.

One thing you may want to check and that is whether there is any leading and/or trailing spaces on your split apart data. Based on the same text you posted in Message #1 , I think there will be. The way to get rid of them is to first replace "space/tilde/space" text string with just a "tilde" symbol, then follow that up with the "Text to Columns" suggestion. To do that, you can press CTRL+H to bring up the Replace dialog box. Specifying the tilde character in the "Find what" field is a little bit tricky as the tilde has special meaning to the Replace dialog box (it is how you tell it to treat the next character as the character without special meaning and is used to turn wildcard characters into normal characters for the find process. So, to search for a single tilde, you must use two consecutive tilde characters. That means you need to type this into the "Find what" field...

" ~~ " {without the quote marks)

To be clear, that is a space followed by two tilde characters followed by another space. You only need to put a single tilde character in the "Replace with" field. You should also click the "Options>>" button and make sure the "Match entire cell contents" checkbox is not checked. Okay, if you now click the "Replace All" button, your fields should now be delimited by a single, lone tilde symbol which you can now use in "Text to Columns" as the delimiter in the "Other" checkbox's field.