# extract negative value from a alphanumeric string

i have a data in column A and i need all the numbers proceeding with negative to be extracted in column B
plz check the example given
LVL 6
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Try this:

in B1 cell write this formula:

=Mid(A1,Find("-",A1),Find(" ",Mid(A1,Find("-",A1))) - Find("-",A1))

Then extend the formula on B1 to B5

Hope it helps. Regards.
Author Commented:
it gives me error the formula cannot be entered
Commented:
Use this formula in A2...

``````=MID(SUBSTITUTE(A2,"- ","-"),FIND("-",SUBSTITUTE(A2,"- ","-")),FIND(" ",SUBSTITUTE(A2,"- ",""),FIND("-",SUBSTITUTE(A2,"- ","-"))+1)-FIND("-",SUBSTITUTE(A2,"- ","-")))
``````

Saurabh...
Author Commented:
thanks Saurabh  but it does not work.

it results
-32114
-42
-254
-331

while it should result
-32114
-42
-2541
-3311
Commented:
Try this in B2 and copy down:

=LEFT(CONCATENATE(IF(IF(ISERR(FIND("-",A2)),0,FIND("-",A2))=0,"",MID(A2,IF(ISERR(FIND("-",A2)),0,FIND("-",A2)),100))," "),FIND(" ",CONCATENATE(IF(IF(ISERR(FIND("-",A2)),0,FIND("-",A2))=0,"",MID(A2,IF(ISERR(FIND("-",A2)),0,FIND("-",A2)),100))," "))-1)
Author Commented:
thanks Missus

it worked up to the third one but failed in the last one , the result i got is the following

-32114
-42
-2541
-

it could not produce the -3311
Author Commented:
attached is the sample workbook with the data in it
Book1.xlsx
Commented:
You can use the following code...

``````=MID(SUBSTITUTE(A2,"- ","-"),FIND("-",SUBSTITUTE(A2,"- ","-")),FIND(" ",SUBSTITUTE(A2,"- ","-"),FIND("-",SUBSTITUTE(A2,"- ","-"))+1)-FIND("-",SUBSTITUTE(A2,"- ","-")))
``````

Saurabh...
Book1.xlsx

Experts Exchange Solution brought to you by