Extracting Specific Numbers from a Cell with Multiple Text Strings and Numbers

[mchoi]

Hello

I am having a problem extracting specific numbers from a cell with multiple text strings and numbers.
I've copied the data from a PDF file and it appears in a single cell like this:

HIG Middle Market LBO Fund II 1.8 1% 2014

What I am looking for is columns with Fund Name (HIG Middle Market LBO Fund II), Fund Size (1.8), and Fund Status/Year (2014). The 1% piece is not important to me.

I've tried to find ways to separate the data but I really can't seem to make it work. The formula that has worked for me in the past is:

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN($A$2))),1))*ROW(INDIRECT("1:"&LEN($A$2))),0),ROW(INDIRECT("1:"&LEN($A$2))))+1,1)*10^ROW(INDIRECT("1:"&LEN($A$2)))/10)

It doesn't seem to work in this case and I'm at a loss for other solutions. Any help is appreciated.

Thanks.

 

[strooman]

First start with Text to columns, choose fixed width and get rid of the vertical lines (arrows) you don't need.

When this does not work, post a small sample of your data.

 

[bosco_yip]

Try :

1] A1 : HIG Middle Market LBO Fund II 1.8 1% 2014


2] Fund Name, B2 :

=LEFT(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&5^19))-1)

3] Fund Size C2 :

=-LOOKUP(1,-LEFT(SUBSTITUTE(A3,B3,),{1,2,3,4,5}))

4] Fund Year, D2 :

=-LOOKUP(1,-RIGHT(SUBSTITUTE(A3,B3,),{1,2,3,4,5}))

Or,

IF you want to spilt the number in separated columns only, then B2, enter the formula , copy across :

=TRIM(LEFT(RIGHT(SUBSTITUTE($A2," ",REPT(" ",30)),(5-COLUMN(B:B))*30),30))

Regards

 

[Rick Rothstein]

On the off-chance that your Fund Name could contain numbers in it (that would make one of bosco_yip's formulas not work), give these formulas a try...

[table="width: 500"]
[tr]
[td]

[B]Fund Name:[/B] =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),1,100*(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2)))

[B]Fund Size:[/B] =0+TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),300),100))

[B]Fund Year:[/B] =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

[/td]
[/tr]
[/table]

Note: The above formulas each assumes the text in cell A1 will not be longer than 100 characters total... if it could be longer, than make each 100 larger than the longest text you could possibly have (the 300 would be three times that number).

 

[mchoi]

Bosco_Yip, your formula seems to do the trick, as does yours Rick.

Thank you all for the help.