Extract multiple values from long strings of text

**Patrick791** · 10-04-2012, 09:53 AM

Hello,

I am trying find specific strings in long strings of text contained in various rows. I tried writing a formula to do this but received a #Value! error. Below is an example of what I am trying to accomplish and the equation I used

Trying to extract the values (4/0, #6, 350, 1000) and put them into Column 2

Column A------------------------------------Column B
4/0-19w H19 1350 Comp----------------------4/0
#6-7w H16 1350 Cmpt------------------------#6
350 kcmil 37w H221 8177 Conc---------------300
1000 kcmil 61w H19 Unilay Comp------------1000

I have used the following code but I could not get it to work. I will read the very first value in the code (4/0) but when I drag the formula down. It will only give me the values that are 4/0 and none of the other ones

=IF(SEARCH("4/0",A1),"#4/0",IF(SEARCH("#6",A1),"#6",IF(SEARCH("300",A1),"300",IF(SEARCH("1000",A1"),"1000")))))

Can I use a formula like this to achieve this result? Or do I have to write a macro?

Thanks,
Patrick

**venkat1926** · 10-05-2012, 02:48 AM

not clear obviously you did not give complete information
why not just type this formula in an empty cell

="4/0"&"#6"$350$1000

**Pepe Le Mokko** · 10-05-2012, 02:57 AM

One way :Select your column
Ctrl + H
replace what -
replace with (space)
OK
Then Data - text to columns - delimited - Next -Select " space" - Next - Select " do not import" for each column except first - OK