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