LinkBack URL
About LinkBacks
I tried to format 1,234.56 instead 1.234,56 just Find-Replace but doesen't help.
Help me.
I use Office 2010.
File:
Attachment 420260
NUMBER.png
I tried to format 1,234.56 instead 1.234,56 just Find-Replace but doesen't help.
Help me.
I use Office 2010.
File:
Attachment 420260
NUMBER.png
Last edited by Sheldon Cooper; 09-20-2015 at 04:44 PM.
Your picture doesn't show up.
Don't think you can do that with formatting, you would need to change your regional settings to one of the European formats.
Regional settings are likely the cause. Check that you have the correct regional settings then go back and select the values and format correctly.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
In set it in Control Panel, but nothing.
decimal-list-separators.pngalso.pngchecked.png
Any solution? There is a lot of numbers, I can't edit them all.
Many members are unable to see images in the *.png format.
If you need to post an image post it in the *.jpg format.
Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Try this formula
=--SUBSTITUTE(SUBSTITUTE(A1,".",","),",",".",2)
Data Range
apply desired format
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I don't understand you, sorry. Can you explain?Originally Posted by AlKey
Here is a file:
Price.xls
Not really sure what needs to be explained. Please see attached file.
Hello,
looks like your data is stored as text. One way to convert it to numbers is with the Text to columns Wizard.
- Select all data in the column
- on the Data ribbon click "Text to Columns"
- Click Next
- Click Next. Now you should be in Step 3
- click the Advanced button and check that the Decimal and Thousand separators are the ones that the data currently USES. In your case, it uses the decimal comma and the dot for the thousand separator.
- click OK and click Finish.
2015-09-21_9-25-56.gif
Now the numbers are numbers and will use your regional settings for decimal and thousand character.
cheers, teylyn
It's not a number after all, I did all you told me.
I did it, but I don't need a column with a numbers which are without funcion.
I don't understand. Did you get it to work with the Text to Columns or not? If it did not work, was the data text before you started? Did you put in the delimiters correctly? You need to tell Excel what delimiters the source data is using, not what delimiters you want as the output.
Also, please spend a few more words when replying. It's really hard to read your mind.
Is this problem solved yet?
Ben Van Johnson
Try this. Enter the following formula in B2 and fill down. Format as number using , as 1000 separator.
Formula:
=--SUBSTITUTE(SUBSTITUTE(A2,".",""),",",".")
NUMBER 842.800,00 457.600,00
No, it didn't work.
Yes, the data was text before I started.
I saw picture, I put it right that.
Do you mean "Column data format", I choose General.
Data preview isn't like on your picture.
I set decimal & thousands separator.
I don't know what to say, I tried carefuly all what you had told to me. Isn't a solution.
@newdoverman I want do delete column A, I don't need double numbers (bad numbers & correct numbers, I don't mean A1 when writes NUMBER) in my workbook.
Post a sample file. I don't see why the method I suggested should not work.
=--SUBSTITUTE(SUBSTITUTE(A1,".",","),",",".",2)
This might help..
Try this
1. wrt newdoverman's suggestion, once you use the formula, just copy/paste special from B to A
2. Text to columns method ala teylyn:
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks