asked on

Excel not storing numeric string correctly

Hi experts,
I use Excel 2016 and have a column which has to show sequence numbers of pattern "000" .. For example I should be able to have an entry 001. On the display level it works fine by format cells-> Custom . It is quite fine, if I enter like '001 ( with a prefix apostrophe) . But I can't tell the customer to add an apostrophe when they input.
Is there some plugin that can work for this effect- That is , if a user types 001 it will prefix the apostrophe and store it but will display only as 001 in the cell . ( Something like = "'" & A2 will not help)

Thanks

Roy Cox

If you have set the Custom Format to "000" for the whole column then that format should work constantly. Adding an apostrophe merely converts the number to a String and as such it will not be usable as a number in calculations. You can use a formula, but it would require two columns, a formula cannot refer to the cell it is in.

=Text(A1,"000")

Jacques Geday

Well don't know why it is not doing it your end but to make sure you got it right 'Without' the Quote ' do this:

1) Select the whole column you need to show as 000 and right click choose Format Cell then select General press ok and close it.
2) Select again this same column (to select the entire column click on the Column Letter it will highlight the entire column then again right click now select custom and in the input box type 000 press ok

Now if after step 2 still some data is not showing as 000 then it is maybe an issue with Excel 2016.

Here is the complement. Put this code in the worksheet_change event of that sheet. (Change the Column to reflect your correct column) This example uses column E to forces the formatting. Check the attached workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = Columns("E").Column Then
    Target.EntireColumn.NumberFormat = "000"
    Target = Format(Target, "000")
End If
End Sub

Open in new window

gowflow
Format000.xlsm

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sam OZ

ASKER

Hi,
I have this issue in both Excel 2016 and 2013 . The real problem is, eventhough on display it shows as 001 , the real storage is only as 1 ( Please see the attachment) . So when I am refering this excel on a .NET application, it shows only as 1 instead of 001. I have same issue if I try to export Excel data to MS Access. But if I key in as '001 , that comes fine .
The Macro doesn't help as it is just giving the effect of format which does not help

Regards,
Samson

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial