Dividing text

  • Thread starter Thread starter Paul Hyett
  • Start date Start date
P

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?
 
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
 
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
 
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.
Click to expand...
Thanks - can't believe I didn't think of that!
 
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)


Per Jessen said:
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
Click to expand...
 
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.
 
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.
Click to expand...

Thanks - I'll try that. :)
 
Back
Top