asked on
Scientific notation problem
Good day experts. i have got data in excel. in one of the columns, i have ID numbers which are in the format 12345678D19. The check letter differs from person to person. my problem is if I have a check letter E, excel will recognise my ID number in scientific notation. For instance if I have an ID number such as 08726849E00, excel will recognise it as 8.73E+06. i have tried to format the entire row to text but this failed to yield any result. thank you in advance for your help. I HAVE ATTACHED A SAMPLE
Microsoft OfficeSpreadsheetsMicrosoft ExcelVBA
Last Comment
Shums Faruk
ASKER
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Here is small VBA which will convert your column A to General then you can type long alpha numeric numbers or copy paste special values without any problem
Sub CellFormat()
Dim Ws As Worksheet
Dim r As Long
Application.ScreenUpdating = False
Set Ws = ActiveSheet
r = Ws.UsedRange.Rows.Count
Ws.Range("A2:A" & r).NumberFormat = "@"
Application.ScreenUpdating = True
End Sub
Alternatively you can input a single quote ' in the beginning of the ID like below...
'08726849E00
'08726849E00
The column needs to be formatted before the data goes into the cells.
No comment has been added to this question in more than 14 days, so it is now classified as abandoned.
I have recommended this question be closed as ID: 42066324 as best solution as per Author's acknowledgment
If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
I have recommended this question be closed as ID: 42066324 as best solution as per Author's acknowledgment
If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
No attachment...