Split Text to columns using an entire word, not just a single cha

[tomhomestroops]

I have a file with info in a cell that looks like:

123 South Main St. Suite 6

I want to do text-to-columns, splitting each cell out based on the word
Suite. I can’t do it by a space delimiter because it will break it out in
too many pieces. Can I use the "other" delimiter choice but put in an entire
word rather than just one character?

Any suggestions?

 

[Elkar]

You can only use a single character as a delimiter. But here's something you
could try:

Do a Find/Replace over your range of data.
Find: suite
Replace with: ~suite
Now, use Text-to-Columns, and use the ~ character as your delimiter.

Note, if you happen to have ~ in your data already, pick a different symbol
that doesn't appear.

HTH,
Elkar

 

[bpeltzer]

You could use a couple of formulas with the FIND function to create the
'before' and 'after' portions. If, for instance, your address is in A1, then
=TRIM(LEFT(A1,FIND("Suite",A1)-1)) will get the 'before' part and
=TRIM(RIGHT(A1,LEN(A1)+1-FIND("Suite",A1))) will get the 'after'.
If you put those formulas in B1 and C1, and copy those cells down into as
many rows as you need, you'll wind up with columns B and C showing the split
from column A. Then you could copy / paste values in columns B & C, then
delete column A.
(Note that the formulas shown don't test for the absence of the word Suite
in the address!).

 

[Mallycat]

Use a 2 step process.

First create a formula in J9 =FIND("suite",K9)

Then in H9 put =LEFT(K9,J9-1)

You can trim the right of the text using Right()

Matt

 

[tomhomestroops]

VERY clever! All 3 solutions just posted would work, but this one seems to
be the best for my situation. I'll try it out and let you know if it works,
but it sounds like it should.

 

[tomhomestroops]

SHWEEEET! Or shall I say Suite!

That worked. I had to do a second search/reaplce once I parsed it to remove
an extra space in the 2nd block, but it worked fine.

Excellent solution.

 

[tomhomestroops]

VERY Clever. Downright elegant.

That worked fine. Thanks much.

 

[tomhomestroops]

Sorry about the multiple posts. This is the first time I used this forum and
I thought my prior posts had disappeared into the ether.

 

[dannyfromnj]

bpeltzer makes a valid point... let me expand on it a bit.

Text to columns along with Search & Replace are fantastic and work
great in for many situations, but there are also times when data you'd
like to manipulate doesn't necessarily fit into a generic model, or
have an ongoing need that would be best resolved by using an Excel
Function. Well, not to worry, Excel has many tricks up it's sleeve to
help you in almost every situation.

The tricks I speak of with regard to your question are what's known as
Text Functions. See below for a short list and general format.

LEFT - LEFT(text; num_chars)
MID - MID(text;start_num;num_chars)
RIGHT - RIGHT(text; num_chars)
SEARCH - SEARCH(find_text;within_text;start_num)
LEN - LEN(text)

Alrighty then, using some of Excel's powerful Text functions... lets go
get us some text shall we? Buckle up!

Examples:

Using your text "123 South Main St. Suite 6" and

Text is assumed to be in cell A1
Formula can be placed in any cell except A1

The following formula returns the first word or block of characters
from a string, which in this case is "123"

=LEFT(A1,FIND(" ",A1)-1)

The following formula returns all the characters in a cell preceding
the occurrence of "St", which in this case is "123 South Main St"

=LEFT(A1,FIND("St",A1)+1)

The following formula returns the last word or block of characters in a
string, which in this case is "6"

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"
","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

The following formula searches for " Suite", trims the leading empty
space character, returning "Suite" and all the words or block of
characters that follow, which in this case is "Suite 6"

=RIGHT(A1,LEN(A1)-FIND(" Suite",SUBSTITUTE(A1,"
","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

The following formula returns all but the first word or block of
characters in a string, which in this case is "South Main St. Suite 6"

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"
","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","",1)))))

Hope you found this useful!

Danny Tedesco
http://www.wirelessphonecity.com
Cut the cables and let your data fly...