Forum Discussion
Representation of numbers in cell different to formula bar
Hi,
I have an issue in a dataset where the values format in the cell is different from the value in the formula bar. Please see below:
This is inconsistently present as an issue throughout the dataset. Is there any way I can get the formula bar to recognise the value in the cell (D2) as a decimal?
Best,
Alfie
- If some already have a point after the first number, then only Cell Format Custom
Enter: 0"."000
But if the existing point is not the first number, then probably only through VBA code.
5 Replies
Assume this is text. On a quick note, you could use the cell with the number divided by 1000 if the numbers are four digits and there is a period after the first number. Example: =A1/1000
or as Mr. Riny_van_Eekelen already mentioned,
Cell Format Custom
Enter: 0"."000- Ah, I understand. Yes that's great, both of those are fixes. Thank you both. One issue still is that I need to fix quite a lot of these cells but some are correct already. Is there anyway I can separate out this correct data/ignore it when applying these fixes? Either the formatting or the formula...
- If some already have a point after the first number, then only Cell Format Custom
Enter: 0"."000
But if the existing point is not the first number, then probably only through VBA code.
Alfieb1996 Looks like a custom format in D2 and perhaps others, but not all.
If you want to have a decimal number (3 decimals I suppose) you need to enter it with the decimal point or us the Advanced setting under Editing options to "Automatically insert a decimal point, 3 places".
- Hello,
Thanks for your response! Entering it isn't an option as I have many entries to change. I did try the Advanced setting but it didn't seem to alter how the number is represented in the formula bar unfortunately.
it's really frustrating as some numbers display correctly...thanks for your reply once again.
