A friend has entered a long list of amounts in a column in Excel 2003.
Unfortunately, he entered the figures as set out below and Excel thinks the
numbers with a fullstop after the thousands are text:
1.234.56
3.567.89
354.87

I have tried several ways to fix the errors, e.g., Replace, Substitute
functions, but without success. I do not know how to do macros but I think
one could be the answer. Any help would save him lot of work and would be
greatly appreciated.


Denis

Hi Eddie,

Am Tue, 2 Aug 2011 22:04:24 +1000 schrieb Eddie O'Shea:

A friend has entered a long list of amounts in a column in Excel 2003.
Unfortunately, he entered the figures as set out below and Excel thinks the
numbers with a fullstop after the thousands are text:
1.234.56
3.567.89
354.87

the values are in column A, then try in another column:
=IF(LEN(A1)6,SUBSTITUTE(A1,".",",",1),A1)
Copy the new column and then paste values to the origin place


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

You getting your data entry done in India? <g

If your numbers are always < 999,999 then in an adjacent column use this formula...
=IF(ISTEXT(G1),--(SUBSTITUTE(G1,".",",",1)),G1)
Assumes data starts in cell G1.
After entering the formula and filling down, copy the "adjacent" column and paste values.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...) .



"Eddie O'Shea"
wrote in message
u...
A friend has entered a long list of amounts in a column in Excel 2003. Unfortunately, he entered
the figures as set out below and Excel thinks the numbers with a fullstop after the thousands are
text:
1.234.56
3.567.89
354.87

I have tried several ways to fix the errors, e.g., Replace, Substitute functions, but without
success. I do not know how to do macros but I think one could be the answer. Any help would
save him lot of work and would be greatly appreciated.
Denis

Here is another formula that you can use to generate the corrected values in
order to Copy/PasteSpecial/Value them over top of the existing values)...

=SUBSTITUTE(A1,".","")/100

Then copy/




"Eddie O'Shea" wrote in message
u...

A friend has entered a long list of amounts in a column in Excel 2003.
Unfortunately, he entered the figures as set out below and Excel thinks the
numbers with a fullstop after the thousands are text:
1.234.56
3.567.89
354.87

I have tried several ways to fix the errors, e.g., Replace, Substitute
functions, but without success. I do not know how to do macros but I think
one could be the answer. Any help would save him lot of work and would be
greatly appreciated.


Denis

Here is another formula that you can use to generate the
corrected values in order to Copy/PasteSpecial/Value them
over top of the existing values)...

=SUBSTITUTE(A1,".","")/100

Then copy/

I have no idea what happened to my posting... misplaced text, no signature
line??? Anyway, the idea should be obvious given the previous postings...
ultimately, I just wanted to offer that simpler formula for your
consideration, that is all.

Rick Rothstein (MVP - Excel)

Thanks for the replies, which were all helpful. There were blank cells in
the columns, which I did not mention in my email. That caused me a further
problem and I found that Jim Cones’ solution put a zero in the blank cells,
rather than an error message. Thanks again.

Eddie


"Eddie O'Shea" wrote in message
u...
A friend has entered a long list of amounts in a column in Excel 2003.
Unfortunately, he entered the figures as set out below and Excel thinks
the numbers with a fullstop after the thousands are text:
1.234.56
3.567.89
354.87

I have tried several ways to fix the errors, e.g., Replace, Substitute
functions, but without success. I do not know how to do macros but I
think one could be the answer. Any help would save him lot of work and
would be greatly appreciated.


Denis