Excel Macro

[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

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

 

[Claus Busch]

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

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Rick Rothstein]

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

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

 

[Rick Rothstein]

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)

 

[Eddie O'Shea]

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