Extract info from text strings

[Colleyville Alan]

I have some financial information that I can paste from a mainframe
application to Excel. I would like to take the string information and break
it into several columns. I have tried nested IF statements with FIND
statements embedded in them, but I was wondering if there was a simpler way.

Example - the string comes from the main frame looking like this:

FNMA 6.50% 3/15/04 100000.00
FHMLC 6.50% 3/15/04 100000.00
U S TREASURY 5.75% 8/15/04 400000
FNMA 6.50% 8/15/04 100000.00
HOME DEPOT 5.00% 2/1/05 20000

I'd like the output to be in separate columns:
BondName Yield Maturity ParValue

Using the Data|"Text to columns" menu option will not really work since
neither delimiters nor spacing is consistent.

What would work is if I could extract the bond name first and separate it
from from the rest of the data. Then I could split the other data with Text
to Columns. Is there an easy way to do that or is there another way to
achieve my goals. I could write a macro, but I'd rather use worksheet
functions if possible.

 

[Peo Sjoblom]

Alan,

one possible way, first to extract the bond names
assume your data starts in A1

=TRIM(LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1))
,0)))

entered with ctrl + shift & enter

copy down as long as needed, now to extract the numbers only

=SUBSTITUTE(A1,LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1
)))+1,1)),0)),"")

entered with ctrl + shift & enter


that will give you 2 columns, now copy the columns and paste special as
values in place,
that will give you one column looking like

FNMA
FHMLC
U S TREASURY
FNMA
HOME DEPOT

and one

6.50% 3/15/04 100000.00
6.50% 3/15/04 100000.00
5.75% 8/15/04 400000
6.50% 8/15/04 100000.00
5.00% 2/1/05 20000

now you should be able to use text to columns on the the second column (at
least I could
using the example), use space as delimiter and in step 3 select the middle
part and select date MDY
under column data format or else some probably will come in as text. That
will give you four fields


HTH

 

[Colleyville Alan]

Alan,

one possible way, first to extract the bond names
assume your data starts in A1

=TRIM(LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1))
,0)))

entered with ctrl + shift & enter

copy down as long as needed, now to extract the numbers only

=SUBSTITUTE(A1,LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1
)))+1,1)),0)),"")

entered with ctrl + shift & enter


that will give you 2 columns, now copy the columns and paste special as
values in place,
that will give you one column looking like

FNMA
FHMLC
U S TREASURY
FNMA
HOME DEPOT

and one

6.50% 3/15/04 100000.00
6.50% 3/15/04 100000.00
5.75% 8/15/04 400000
6.50% 8/15/04 100000.00
5.00% 2/1/05 20000

now you should be able to use text to columns on the the second column (at
least I could
using the example), use space as delimiter and in step 3 select the middle
part and select date MDY
under column data format or else some probably will come in as text. That
will give you four fields

Thanks - the solution worked great!

 

[RWN]

Assuming the input cell is A1 the following will split the fileds for you;

BondName =LEFT($A1,FIND("%",$A1)-6)
Yield =TRIM(MID($A1,FIND("%",$A1)-5,5))
Maturity =MID($A1,FIND("%",$A1)+1,FIND("
",MID($A1,(FIND("%",$A1)+2),10)))
ParValue =TRIM(RIGHT($A1,LEN($A1)-(FIND($D1,$A1)+LEN($D1))))

--
Regards;
Rob

Please reply to the NG, I'm already up to my eyeballs in Nigerian/South
African get rich letters
as well as "Microsoft Critical Updates" et al.

 

[Colleyville Alan]

Assuming the input cell is A1 the following will split the fileds for you;

BondName =LEFT($A1,FIND("%",$A1)-6)
Yield =TRIM(MID($A1,FIND("%",$A1)-5,5))
Maturity =MID($A1,FIND("%",$A1)+1,FIND("
",MID($A1,(FIND("%",$A1)+2),10)))
ParValue =TRIM(RIGHT($A1,LEN($A1)-(FIND($D1,$A1)+LEN($D1))))

Thanks - this is easier still