Convert numbers that have hidden Quotation Mark Embedded

C

chom krusopon

Every month I work with a worksheet sent to me from another program. The
numbers look like these:

Amount
$44,000.00
$90,000.00
$115,000.00
$24,000.00

I tried coverting them to nubmers by first using =value() formula, that
doesn't work; I tried by formatting the cell to numbers, that doesn't work; I
tried using text data to column and that doesn't work neither. I ended up
having to first hit F2 to call up the numbers in each cell, then backspace
the cell 3 times and that method works. However, it's time consuming.

Anyone has a better idea? Please help. Thank you so much. Chom
 
P

pinmaster

Try this:
=TRIM(A1)
copy down as far as needed
then copy the data, paste special, values.

Hope this helps!
JG
 
C

chom krusopon

Thanks for your help. But =trim() formula doesn't work neither. Any othre
ideas. I will be etnerally grateful for anyone who could help me.
 
S

SludgeQuake

Perhaps some variation of the following formula would work (where the data is
in cell A2):
=value(mid(A2,2,Len(A2)-4))
I'm not sure how Excel will treat the initial dollar sign. So perhaps
instead of starting the mid function on position 2 it will need to start on
position 1 which might look like
=value(mid(a2,1,Len(a2)-3)
I'm afraid I couldn't duplicate the issue you described, but I hope one of
these formulae will help.
Best of luck,
- Sludge
 
A

Arvi Laanemets

Hi


Select the range with data. Format as General. ReplaceAll "$" with nothing
(I suspect the $ character is a literal, not displayed by formatting).
 
C

Chris

I was able to replicate your situation using the formula below with a slight
modification.

=value(mid(A2,2,Len(A2)-5)), which removed both the $ and 4 extra spaces.
 
P

pinmaster

Ok, try this:
type the number 1 in a cell, now copy that cell, next select your data then
Edit/Paste Special and select Multiply hit ok.

HTH
JG
 
C

chom krusopon

Thank you so much. It works beautifully. Chom

SludgeQuake said:
Perhaps some variation of the following formula would work (where the data is
in cell A2):
=value(mid(A2,2,Len(A2)-4))
I'm not sure how Excel will treat the initial dollar sign. So perhaps
instead of starting the mid function on position 2 it will need to start on
position 1 which might look like
=value(mid(a2,1,Len(a2)-3)
I'm afraid I couldn't duplicate the issue you described, but I hope one of
these formulae will help.
Best of luck,
- Sludge
Click to expand...
 
Top