ExcelBanter - View Single Post - Extract Part of Sentence for Separate Cell's Value

Ron Rosenfeld

On 21 Dec 2006 06:54:54 -0800, wrote:

Hello Gents/Ladies...another question....

I have a cell, C13, that says:

Bonus: $4,000 (DEC06).

My question:
How can I copy just 4,000 from this cell and make K18 say $4,000.

FYI, C13 does not always contain this data, just sometimes. I need
something that would look for the dollar amount and copy only the
dollar amount to K18.

Thanks for all your help, I really appreciate your time.

:o)
Bull

Your specifications are incomplete. In particular, you do not indicate what
you wish to happen should the data you describe not be in the cell.

To copy the value 4000 (not the text string 4,000) to cell K18 and "make K18
say" $4,000:

1. Format K18 as currency with zero decimal places.
2. Put the following formula in K18:

=--MID(C13,FIND("$",C13)+1,FIND(" ",C13,
FIND("$",C13)+1-FIND("$",C13))-1)

This formula will give a VALUE error if C13 does not contain the appropriate
data. But you'll have to tell us what you want to happen in that instance.

--ron