Extract info from text strings

  • Thread starter Thread starter Colleyville Alan
  • Start date Start date
C

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.
 
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
 
Peo Sjoblom said:
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
Click to expand...

Thanks - the solution worked great!
 
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.
 
RWN said:
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))))
Click to expand...

Thanks - this is easier still
 
Back
Top