Problem when using find & replace

[amr7]

Hi guys,

I have a a problem when replacing , with . for large numbers like this 1,564,548,54 all commas converted to dots, so the number aligned to left.

Thanks in advance

[elayaz]

Hello,

Can you give some more details. If you want to replace all commas with dots, then try this. (assuming your values in column A)

=SUBSTITUTE(A1,",",".")

[amr7]

I use 2 methods for converting commas to dots, either by find and replace or by using Alt+A+E and it sound good for numbers with 1 comma 1,00, but when number contains more than one comma 1,846,74 so 2 commas converted to dots it became 1.845.47 which also invalid in excel, I tried to use Substitute formula, but there is something wrong, I'll attache sheet
Regards,
Amr

[elayaz]

Are you trying to replace all commas with dot OR only the last comma with a dot?

[amr7]

the last comma only

[Debraj Roy]

are you talking about, col P, Q, R, S

[elayaz]

Okay. Then Try this

Please Login or Register  to view this content.

[amr7]

yes Col P, Q, R, s

[amr7]

Okay. Then Try this

Please Login or Register  to view this content.

it doesn't work

[Debraj Roy]

Lets try..

Little bit Tricky.. so follow each step properly..

* Office Button..
* Excel Option
* Advanced
* editing Option.
* Un-check Use System Separtors.
Set Decimal Separator > "," without Quote
Set Thousand Separator > "." without Quote

OK..

Now come back to Excel Sheet..
* Write 1 in a blank area.
* Copy... cell Contraining 1.
* Select Column PQRS, containing only numbers you want to convert.
* In you case "P7 to Q2496"
* Paste > Special > Multiply (Alt E S M)
* It will convert all TEXT digits to Numeric.. and will change all "," to Decimal Separator.

* Now you can re-set your Decimal & Thousand Separator by selecting "Use System Seprator" from excel Option > Advance Option..

by the way.. a simple macro can do it much faster.. but try to understand International Separator's for future reference..

[jhren]

the last comma only

I'm assuming you want to change these as numbers and their formatting. If so, forget about find and replace.

Go to File>Options>Advanced. In "Editing options" section, untick "Use system separators" and put comma in Decimal separator, period in Thousands separator. OK out of dialog.

On your worksheet, select all that appear to be numbers but are left aligned (which generally speaking, are numbers entered as text). There should be a diamond icon menu indicator beside the upper left selected cell. Click on it and choose "Convert to number".

Now go back to File>Options>Advanced and tick "Use system separators".

Format your [new] number cells to for desired display


DARN that Debraj Roy for posting while I was typing

[elayaz]

The formula just changes the last comma to dot. I am not sure the decimal mark system of Egypt.

Except the last decimal mark, which should be "dot" all the others are "comma"?. Please post example results in the work sheet itself.

[elayaz]

See if this works.

[amr7]

Thank you Debraj Roy, jhren it's very helpfull to me

[amr7]

The formula just changes the last comma to dot. I am not sure the decimal mark system of Egypt.

Except the last decimal mark, which should be "dot" all the others are "comma"?. Please post example results in the work sheet itself.

Thanks a lot elayaz the equation in the sheet you attached is true and i use it , but i think it's little bit complicated and i don't understand it