capturing text to the right

  • Thread starter Thread starter dstiefe
  • Start date Start date
D

dstiefe

Remaining Balance on Invoice: $1,000.00

the text above is in a cell..i have thousand of cells in column like this

how do i capture just the "$1,000.00" amount. because this amount is
sometimes a 1,000 and sometimes 10,000

Thank you
 
Hi,

Try this but it's still text

=MID(A1,FIND("$",A1),999)

or this to drop the $ sign

=MID(A1,FIND("$",A1)+1,999)

Mike
 
If they're all in the same column, and there is no text after the $ amount,
then the simplest approach would be a text-to-columns operation with "$" or
":" delimiter, depending on how reliably consistent the preceding text is.
You can the arguments all set the right way using the macro recorder...
 
You have a few choices...

If the the text is always the same, you could
select the column
Data|text to columns|delimited by colon (:)
and even skip the first field and just return the second field.

If the text always contains that ": " (colon space character):
=--mid(a1,search(": ",a1,1)+2,255)
The 255 is just a big enough number to retrieve everything after the ": ".

=mid() returns a string.
The -- stuff changes the text to a real number.

If the text will vary, but you always want to pick off the last piece:
=--trim(right(substitute(trim(a1)," ",rept(" ",99)),99))
 
Back
Top