#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Excel Macro

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


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Excel Macro

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,549
Default Excel Macro

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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,934
Default Excel Macro

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,934
Default Excel Macro

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)



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Excel Macro

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 1 February 5th 07 10:31 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 3 February 5th 07 09:22 PM
passing arguments from an excel macro to a word macro KWE39 Excel Discussion (Misc queries) 1 July 7th 05 03:56 PM


All times are GMT +1. The time now is 01:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"