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

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

Hope this helps!
JG

"chom krusopon" wrote:

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

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.

"chom krusopon" wrote:

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

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


"chom krusopon" wrote:

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

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


"chom krusopon" wrote:

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

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.


"SludgeQuake" wrote:

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


"chom krusopon" wrote:

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

Thank you so much. It works beautifully. Chom

"SludgeQuake" wrote:

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


"chom krusopon" wrote:

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

Hi


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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"chom krusopon" wrote in message
...
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