Dividing text

[Paul Hyett]

I know about the text-to-columns function, but if I have a sentence of
text with each word separated by a space, it'll split across as many
columns as there are words in the source cell.

If I want to split on a specific 3 character sequence (namely " - "), is
there any way I can do so by using the various text functions to
determine where in the sentence that exact sequence occurs, and
splitting only at that point?

 

[Per Jessen]

Hi Paul

As you may have discovered, the 'TextToColumns' function can only split on
one character, so I suggest you use Find/Replace to replace your 3 character
sequence with a single unique character.

Then use TextToColumns with the single unique character as delemitter.

Regards,
Per

 

[Billy Liddel]

You could split on just the - character

e.g. 123 456 - 789 will return
"123 456 " with an extra space that would nedd to be converted using the
trim function.
789 is shown as a number that will need to be formatted as text.

else with g5 as the holding cell:

=LEFT(G5,FIND("-",G5)-2)
=MID(G5,FIND("-",G5)+1,99999)

HTH
Peter

 

[Paul Hyett]

Hi Paul

As you may have discovered, the 'TextToColumns' function can only split
on one character, so I suggest you use Find/Replace to replace your 3
character sequence with a single unique character.

Then use TextToColumns with the single unique character as delemitter.

Thanks - can't believe I didn't think of that!

 

[Rick Rothstein]

Actually, it can split on multiple characters... just checkmark the
characters (in this case, the "Space" checkbox and the "Other" checkbox with
a dash placed in the blank field and, the most important part, put a check
mark in the "Treat consecutive delimiters as one" checkbox. With the "Treat
consecutive delimiters as one" checkbox checked, the space/dash/space will
be treated as a single delimiter and the text will split as desired.

--
Rick (MVP - Excel)

Hi Paul

As you may have discovered, the 'TextToColumns' function can only split on
one character, so I suggest you use Find/Replace to replace your 3
character sequence with a single unique character.

Then use TextToColumns with the single unique character as delemitter.

Regards,
Per

 

[Rick Rothstein]

Checkmark the characters (in this case, the "Space" checkbox and the "Other"
checkbox with a dash placed in the blank field) and put a check mark in the
"Treat consecutive delimiters as one" checkbox... now the space/dash/space
will be treated as a single delimiter and the text will split as desired.

 

[Paul Hyett]

Actually, it can split on multiple characters... just checkmark the
characters (in this case, the "Space" checkbox and the "Other" checkbox
with a dash placed in the blank field and, the most important part, put
a check mark in the "Treat consecutive delimiters as one" checkbox.
With the "Treat consecutive delimiters as one" checkbox checked, the
space/dash/space will be treated as a single delimiter and the text
will split as desired.

Thanks - I'll try that.