Extracting numbers from long text strings

[DanQuinn]

I have a column full of data (dumped from a source where I have no control) where the text is presented as follows...

Random Word Date Random Text 2.245% Random Ending

The length of the text is not consistent.
The position of the number is not consistent.
The number of decimal places of the % value is not consistent.
The % value is what I want but there may be other numbers in there as well (as part of date descriptors, etc.) - but at least there's only one percentage.
Not all the lines have percentages (need to produce blank instead of error).

1. Is there a way of extracting out just the % value (with or without the % sign, doesn't matter) and leaving it formatted as a number instead of text?

2. I know I can create a simple follow-me macro where I enter the cell, go into edit mode, search for a %, hit ctrl-left to get back to the previous space, marquee select to the following space, copy, right-cell, paste...but hoping for a more elegant solution.

Thanks for your help all!

 

[Marcelo Branco]

Hi,

Try this in B1

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",100)),SEARCH("%",SUBSTITUTE(" "&A1," ",REPT(" ",100)))-50,100))

HTH

M.

 

[Haseeb Avarakkan]

Try,

=TRIM(RIGHT(LEFT(SUBSTITUTE(A1&" 0%"," ",REPT(" ",100)),FIND("%",SUBSTITUTE(A1&" 0%"," ",REPT(" ",100)))),100))+0

 

[DanQuinn]

Thanks all! Worked perfectly!

Now if only I could sort out the garbage of the date in the same field...(sometimes in one format, sometimes in another, spaces here and there...total garbage).