Convert numbers that have hidden Quotation Mark Embedded
 

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

Convert numbers that have hidden Quotation Mark Embedded
 

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

Convert numbers that have hidden Quotation Mark Embedded
 

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

Convert numbers that have hidden Quotation Mark Embedded
 

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

Convert numbers that have hidden Quotation Mark Embedded
 

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

Convert numbers that have hidden Quotation Mark Embedded
 

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

Convert numbers that have hidden Quotation Mark Embedded
 

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

Convert numbers that have hidden Quotation Mark Embedded
 

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