Number formatting help needed.

[NickDP71]

Hello

I am trying to change a list of numbers with 3,4 or 5 decimal places from dot format to commas in Excel 2010.

For example I have:

4.41963
2.94957
3.3642
2.94957

If I convert to text and search and replace dots with commas I get:

441963
294957
33642
294957

What I am trying to get is

4,41963
2,94957
3,3642
2,94957

Anyone know how to do this?

Thanks
Nick

[amy_d2]

hi Nick,

Crtl + H , in find filed put . relace with blank then put comma

Thanks,
A

[NickDP71]

Thanks, it then formats to whole number with comma separators though i.e:

441,963
294,957
33,642
294,957

[Sam Capricci]

Hi amy_d2, it appears it is more tricky than a find and replace as I've tried it several times and it changes 4.41963 to 441963 or 441,963 depending on which choices I make. I think the answer will be in using a substitute formula that i'm going to work on next.

[sparrow1]

Hi,

As far as I know Excel does not have its own regional settings. It just takes whatever is set in the regional settings in the control panel on your PC.

To change the Local Setting go to the Startbutton, Control Panel, Language & Reginal settings is the Number and Currency format settings, these I believe are used by Excel to display the data within a workbook. Excel does not hold 'dots' (. periods, fullstops) within a number, but uses the above formats to display numbers as required.

Hope it helps,

[Sam Capricci]

I tried this and it worked for me...
=SUBSTITUTE(SUBSTITUTE(a2,".",",",1),",""",1)
where A2 is the cell with 4.41963.
just copy down.

[Sam Capricci]

This should also work, a little more compact...
=SUBSTITUTE(A2,".",",")
If this solved your problem please mark as solved. And any kudos would be nice too.

[XOR LX]

Not sure I understand. If you want the numbers to still retain their original values but be in comma format, then you'll be disappointed by the substitute formula-solutions and the like, as these will leave you with nothing but text strings.

And coercing them into numerical values will not have the desired effect: the results of e.g.

=SUBSTITUTE(A2,".",",")+1 and

=SUBSTITUTE(SUBSTITUTE(A2,".",",",1),",""",1)+1

where A2 contains e.g. 4.41963 will be 441964, and NOT 5.41963.

Regards

[Sam Capricci]

I certainly can't argue that you are wrong and I am right, I was just trying to substitute the decimal point for a comma as he requested. If NickDP71 wants to add them next (which he didn't state in his posts) it will not work. I assumed he had a reason to want the format #,##### rather than #.#####. AND, the substitute won't occur in the cell with the original number, he'll have to do it in another column then if he wants to he'll have to replace the originals. If you have a solution that works better I'll be watching to see (and learn).

[NickDP71]

Hi
Ive found that using =SUBSTITUTE formulas does change the values to 4,41963 etc but when I save the file and reopen it reverts to 441,963.
I am looking to have taxt with no number formatting at all to read 4,41963 instead of 4.41963 and stay that way...

[Sam Capricci]

Wonder if that is a particular feature of 2010? I have the substitute formula in col C, your set of #s in col B, did a copy paste special values of the results of the substitute formula into col A, saved it, closed it and reopened it and nothing changed. all the numbers came out the way the were using substitute.
BUT it does list it as number stored as text and changes it if I convert it to numeric.
I'm using excel 2003 and 2007 and it worked. Maybe 2010 just automatically changes it to numeric.

[XOR LX]

I'm using 2010 and, like you, after applying the formula =SUBSTITUTE(A1,".",","), saving, closing and re-opening it has not reverted to a number, though, like I said, any mathematical operations on the cell would force Excel to re-interpret it as 441963.

Regards

[jhren]

Nick,

Is this a special instance, or do you want to use the comma decimal delimiter throughout Excel. If the latter, go to File | Options | Advanced, in the Editing Options section, uncheck "Use system separators", then set "Decimal separator:" to comma, and "Thousands separator:" to a period (or whatever you want to use)...