Text parsing help needed

[Paul Hyett]

I have some text in the format :

'text1-text2 - text3' which I would like to split as follows :

'text1-text2' 'text3' - the way I've been doing it up to now is to use
text-to-columns using '-' as the delimiter, but that is unsatisfactory
as it divides it into three parts, leaving me having to recombine the
first section separately.

(I'm using Excel 2003)

TIA for any help you can offer.

 

[GS]

Paul Hyett brought next idea :

I have some text in the format :

'text1-text2 - text3' which I would like to split as follows :

'text1-text2' 'text3' - the way I've been doing it up to now is to use
text-to-columns using '-' as the delimiter, but that is unsatisfactory as it
divides it into three parts, leaving me having to recombine the first section
separately.

(I'm using Excel 2003)

TIA for any help you can offer.

Did you try using " - " as the delimiter?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[lhkittle]

I have some text in the format :



'text1-text2 - text3' which I would like to split as follows :



'text1-text2' 'text3' - the way I've been doing it up to now is to use

text-to-columns using '-' as the delimiter, but that is unsatisfactory

as it divides it into three parts, leaving me having to recombine the

first section separately.



(I'm using Excel 2003)



TIA for any help you can offer.

Gary's suggestion would be the easiest, but if you want a formula try:

=MID(A1,FIND("- ",A1)+1,LEN(A1)-FIND("- ",A1)+1)

Regards,
Howard

 

[Claus Busch]

Hi Paul,

Am Sun, 10 Feb 2013 07:37:51 +0000 schrieb Paul Hyett:

I have some text in the format :

'text1-text2 - text3' which I would like to split as follows :

with TextToColumns => Fixed Width. Put one delimiter behind text2 and
one in front of text3. In step 3 of the assistent choose for the column
with the hyphen "Do not import column".


Regards
Claus Busch

 

[Paul Hyett]

Hi Paul,

Am Sun, 10 Feb 2013 07:37:51 +0000 schrieb Paul Hyett:


with TextToColumns => Fixed Width. Put one delimiter behind text2 and
one in front of text3. In step 3 of the assistent choose for the column
with the hyphen "Do not import column".

Unfortunately the source text varies in length, so 'fixed width' wasn't
an option.

 

[Paul Hyett]

Paul Hyett brought next idea :

Did you try using " - " as the delimiter?

Unfortunately you can only use one character as a delimiter, not 3.

 

[Paul Hyett]

Gary's suggestion would be the easiest, but if you want a formula try:

=MID(A1,FIND("- ",A1)+1,LEN(A1)-FIND("- ",A1)+1)

Thanks - that extracts the 2nd half, but I also need something similar
that extracts the first half.

 

[Claus Busch]

Hi Paul,

Am Sun, 10 Feb 2013 17:39:39 +0000 schrieb Paul Hyett:

Thanks - that extracts the 2nd half, but I also need something similar
that extracts the first half.

if the hyphen between text2 and text3 has as your example a space in
front and behind try in B1:
= LEFT(A1,FIND("#",SUBSTITUTE(A1," - ","#"))-1)
and in C1:
=SUBSTITUTE(A1,B1&" - ",)

if there are no spaces in fron and behind try:
= LEFT(A1,FIND("#",SUBSTITUTE(A1,"-","#",2))-1)
and
=SUBSTITUTE(A1,B1&"-",)


Regards
Claus Busch

 

[Ron Rosenfeld]

I have some text in the format :

'text1-text2 - text3' which I would like to split as follows :

'text1-text2' 'text3' - the way I've been doing it up to now is to use
text-to-columns using '-' as the delimiter, but that is unsatisfactory
as it divides it into three parts, leaving me having to recombine the
first section separately.

(I'm using Excel 2003)

TIA for any help you can offer.

With string in A1:
Text1-text2: =LEFT(A1,FIND(" - ",A1)-1)
text3 =TRIM(RIGHT(SUBSTITUTE(A1," - ",REPT(" ",99)),99))

 

[Paul Hyett]

With string in A1:
Text1-text2: =LEFT(A1,FIND(" - ",A1)-1)
text3 =TRIM(RIGHT(SUBSTITUTE(A1," - ",REPT(" ",99)),99))

Thank you - that did the trick!

Also, thanks to everyone else who suggested solutions.

 

[Maurizio Borrelli]

Hi Paul,
if, and only if, Text1 Text2 Text3 are single words i.e. without spaces you can try: text-to-columns using ' ' (space) as the delimiter and skip the 2nd column (of 3).

 

[Ron Rosenfeld]

Thank you - that did the trick!

Glad to help. Thanks for the feedback.