Excel Macro

  • Thread starter Thread starter Eddie O'Shea
  • Start date Start date
E

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
 
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
Click to expand...

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
 
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]...
 
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
 
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/
Click to expand...

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
 
Back
Top