Revert Scientific Notiation to the Text that was Entered

[sherryjoo]

We have been recording serial numbers in a user form and storing them into an excel spreadsheet. Apparently some of the serial numbers used this format ***xE***x. Excel recorded it as a exponent. Is there a formula or macro to revert to the originally typed text? I've done a lot of research and almost all of it points to converting it to a number format, but this still wouldn't be what the user originally typed. Maybe even a conversion table? I'm no mathematician and I could use all the help I can get.

Thank you!!!

[sherryjoo]

The format changed when I posted. It four numbers, the letter E and four more numbers. Thanks again.

[SamT]

All values in Microsoft Forms TextBox Controls are Strings. Depending on how they are entered/transferred to an Excel Cell, Values like your Serial numbers may convert to Scientific Notation. You can prevent this by formatting the Excel serial "number" Column as Text.

In VBA:
CStr() will convert any number to a String (Text)
CInt(),CLng(), and CDble() will convert any numerical String to a number.

[Paul_Hossler]

Try something like




ActiveSheet.Range("A1").Value = "'" & UserForm1.Textbox1.Text



Double Quote - Single Quote - Double Quote

You might have to test for a ***xE***x format first and not add the leading single quote if you don't need it

[mikerickson]

You could format the cell as Text, then then entry will be read as text rather than scientific notation

[sherryjoo]

Thank you all for you suggestions. Yes, I have been able to prevent them from continuing to be added to the spreadsheet by using a single quote and ucase, but I need to get the serial numbers back from the previous entries that were entered as scientific. Is there a way to decode the scientific to get the original 1111E1111 back? Can I undo those to get the actual serial number the user entered?
Thanks again!!

[offthelip]

You could try this:
this converts the scientific number in A1 into "serial numbers" in A2


Sub loopt()
Dim str As String


Var = Cells(1, 1)
cnt = 0
Do While Var > 1
Var = Var / 10
cnt = cnt + 1
Loop
str = "'" & CStr(Var * 10000) & "E" & CStr(cnt - 1)
Cells(1, 2) = str


End Sub

[sherryjoo]

This is fantastic!! How would I get it to go through all values in A:A? Thank you so much!

[offthelip]

try this:
Sub loopt()
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Dim str As String

for i = 1 to lastrow
Var = Cells(i, 1)
cnt = 0
Do While Var > 1
Var = Var / 10
cnt = cnt + 1
Loop
str = "'" & CStr(Var * 10000) & "E" & CStr(cnt - 1)
Cells(i, 2) = str
next i

End Sub

[sherryjoo]

Perfection!! Thank you so much!