Thousand Seperator (Not an excel built-in format) - a Real Comma & Spaces between numbers

[Sivashanmugam]

I have a specific requirement, i can't use the built-in format in excel (when i press F2 key, format would not be there), So i need the same format (Spaces & Comma) to be persists when i edit the cell.

Input: 456456456456.00

I need the Output with 2 spaces inbetween each three numbers set (Billions, Millions, Thousands, Hundreds) : 456 , 456 , 456 , 456.00

Output Format: 456<2Spaces>,<2Spaces>456<2Spaces>,<2Spaces>456.00

[LokeshKumar]

Hi,

You are applying wrong approach, what is right approach just use replace command and replace , to space space , space space.
Cheers.......

[Sivashanmugam]

Hi Lokesh, Thanks for your immediate Reply, I appreciate it. i made a mistake in the posting, Input doesn't have commas. Mentioned below is my requirement.

Input 456456456456.00
Output 456 , 456 , 456 , 456.00


Thanks a lot in advance

[LokeshKumar]

Hi,

The formula will not work correctly always since there is decimal values.... but you can follow this trick to get over this problem..

1. Change the custom format of all your data into this 000,000,000,000.00 (press Ctr+1 then go to numbers tab then custom).
2. Once you do that then copy all the data into a notepad (you can see when you copy the data there is comma , after 3 digit).
3. Now create new workbook and press Ctr A and again change the format of all cells into "Text".
4. Now you can paste the data and use the replace command.
5. When you got the appropriate result you can convert data back to number format.

Cheers.....

[Andy Pope]

select range to alter and then execute from immediate window.

Please Login or Register  to view this content.

[Sivashanmugam]

Lokesh, Your solutions works but since i'm going to do this for huge number of records, I'm suspecting, copying & pasting to other application & may match with other record which is irrelevant. if any solution within excel helps much.

[Sivashanmugam]

Andy, Thanks for your reply, I couldn't understand could you help me to figure out how to implement your solution. Probably in sample attachment as an example would help. Thanks.

[Andy Pope]

i converted the immediate window code to a macro

[Sivashanmugam]

Thanks andy for providing it in a Macro enabled excel format. Macro works for me but my values may differ & dont have Standard No. of digits. it may vary like mentioned below,

456,456,456,456.00
456,456,456.00
56,456,456.00
56,456.00
456,456.00
456,456,456.00
56,456,456,456.00
6,456,456,456.00
6,456,456.00
6,456.00

This case, your macro puts zeros in front of the value (in the prefix). Sorry i didn't mention this clearly in my requirement.

[Andy Pope]

change the number format and do replace

Please Login or Register  to view this content.

[Sivashanmugam]

Thank you very much Andy, This code works for me. I just mentioned the range. it provides the output for 55000 cells within a minute.

You made it. thanks a lot.

[Sivashanmugam]

I have a bug on this code... Decimals are not getting retained wherever there is a zero.

In the number formatting, two decimals are there but after we running this macro.. I'm only
getting the output as 860.8, 928.3.. zeros are not getting retained... Please help me out on this.

Output
-------
860.80
928.30

[Andy Pope]

Not a bug just a different requirement.

Excel does not store the trailing zero in numbers. You would need to change the numberformat used in order to diisplay it.

Please Login or Register  to view this content.