Text parsing help needed

P

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

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
 
L

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
 
C

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
 
P

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

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

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
 
R

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))
 
P

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top