Search cell that contains multiple numbers and text and pull out only the negative number

[frenchrich504]

I have a report that runs automatically from a point of sale system which automatically provides a .pdf which I then in turn copy and paste into Excel for easier manipulation. The problem is that when pasted, it automatically places all the information in cell A. I need to be able to extract just the last number from this cell, which is always a negative number (dollar amount), and place it in a separate cell for easier reporting. I used the following formula, in Column D, to extract the needed text from cell A1, =IF(ISNUMBER(SEARCH("text",A1)),"text".

What I need to do now is extract just the last number in that cell which is negative. I could use the same formula, but would have to repeat it hundreds or thousands of times with the increasing dollar amounts in order for this to work. I would need for B1 to display 7.48, B2 to display 21.00, and so forth in this example. Is this possible?

[JohnTopley]

Try

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))+0

Copy down

[Pete_UK]

You could put this in B1:

=--RIGHT(A1,8)

then copy down.

Hope this helps.

Pete

[frenchrich504]

Awesome! Both suggestions worked. Is it possible to have the number that appears be positive? I tried formatting the cells and using the second option down but then it will only display the number in red. If not, no worries this was a huge help!

[JohnTopley]

=trim(right(substitute(a1," ",rept(" ",99)),99))*-1

[Ace_XL]

use

=RIGHT(A1,8)*-1

or to always return positive values

=ABS(RIGHT(A1,8))

[frenchrich504]

That did the trick! Thanks so much, I really appreciate the help!

[Tony Valko]

Is it possible to have the number that appears be positive?

Like this...

=-RIGHT(A1,8)