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

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?

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

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


"tomhomestroops" wrote:

> 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?

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!).

"tomhomestroops" wrote:

> 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?

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.

"Elkar" wrote:

> 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
>
>
> "tomhomestroops" wrote:
>
> > 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?

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.

"Elkar" wrote:

> 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
>
>
> "tomhomestroops" wrote:
>
> > 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?

VERY Clever. Downright elegant.

That worked fine. Thanks much.



"Elkar" wrote:

> 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
>
>
> "tomhomestroops" wrote:
>
> > 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?

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.

"Elkar" wrote:

> 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
>
>
> "tomhomestroops" wrote:
>
> > 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?

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...

I have a long list of contacts in which I need to extract contact's first name, last name, and email address and place them each in their own columns. In some cases, there is no first/last name, in that case first name = temp and last name = company name. Is there a way of singling out this information and have it placed automatically in their own columns?

eg.
"Joe helpfull
Executive board/managing director (Business, corporate or plant management)
Company name
1234 Smallroad, LA
USA

Phone:
+1 243 234 2343
Fax:
+1 234 323 2342
Email:
[email protected]
Internet Address:
http://www.emailaddress.com"

Thanks!