capturing text to the right
 

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

capturing text to the right
 

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

"dstiefe" wrote:

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

capturing text to the right
 

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...


"dstiefe" wrote:

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

capturing text to the right
 

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))

dstiefe wrote:

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

--

Dave Peterson