Share via

Excel VBA Reverse Scientific Notation Algorithm Request

Anonymous
2017-01-01T18:33:59+00:00

To the Community:

I have an Excel worksheet that contains a column of 11-character alphanumeric values that comprise transaction identification numbers taken from an Internet site.  The company which produces these IDs utilize numeric digits along with hexadecimal values ("A" - "F") embedded within the ID.  An example as follows:

202253100E8
20225310E08
20225310E88
2022531E088

Our employee "copies and pastes" these values into an Excel spreadsheet, sometimes in excess of 80k+ records (there is no "download" capability at the website).  When these IDs are pasted into the sheet, some of them (such as the examples above) are converted by Excel into scientific notation caused by the letter "E" embedded in the latter portion of the ID (PLEASE: Do not ask me to instruct the employee on pasting values as text vs. numeric, etc. ... you know us developer's response: USERS!) :)

The above IDs will produce Excel cell values as follows:

TRANSACTION ID EXCEL CELL DISPLAY VALUE EXCEL CELL STORED VALUE
202253100E8 2.02E+16 20225310000000000
20225310E08 2.02E+15 2022531000000000
20225310E88 2.02E+95 2.022531E+95
2022531E088 2.02E+94 2.022531E+94

Can any of the members steer me in the right direction or provide some insight into locating a mathematical algorithm that will reverse the scientific notation back to its original alphanumeric format?

Thank you for any assistance in advance!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2017-01-01T23:15:23+00:00

    James,

    I've looked at the problem a few different ways and it's near impossible to reverse the number. For example, the two different transaction IDs 2022531E088 and 20225310E87 produce the exact same number when entered. Yet they are very different transaction IDs. This proves that it's a one-way conversion and cannot be reversed without making some assumptions or accepting erroneous results.

    My advice is to build an automated Excel application that either pulls the data from the website without human intervention, of build an Excel application that corrects the paste as it is being done. I've done both before. Then, Mr. Boss, instruct your employees how to use the application and make the successful usage of it part of their performance review.

    There is a solution to everything--we just need to look in enough corners to find it.

    Kevin

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-01-01T20:55:56+00:00

    Kevin,

    Thanks for the response.  We do have access to the original data but this will be an ongoing part of our operations in having to download these transactions but, for my wife and I to try and get 100+ employees to do it all the same way is trying to "herd cats".  I have a function that is almost reverse-converting the values but I'm having difficulties when it comes to converting something transaction IDs of the nature "202253100E8" vs "20225310E08" which Excel interprets as almost the same number minus one significant zero...push that zero out 100+ places things get a little scary.

    You may be correct in my not having enough input to reverse-convert the number...it's just that I'm so close I just need to discover that "missing" factor that will get me there if I can at all.  If not, well...it'll be what it is...

    Thanks for reading my request...I appreciate it!

    James

    Was this answer helpful?

    0 comments
  3. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2017-01-01T20:06:55+00:00

    Unfortunately, there are a few too many variables to accurately reverse the conversion. Your only solution is to re-paste the original values into a column formatted as "Text" and using the Paste Values feature.

    Do you have access to the original data?

    Kevin

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-01-01T19:45:51+00:00

    Tasos,

    Thanks for the rapid reply and the link to converting hexadecimal to decimal but, what I am currently searching for is converting the values stored in scientific notation (such as the ones in the OP example) back to their alphanumeric format.  I do not need to actually "convert" the hexadecimal values as I had stated, only that our vendor "uses" hexadecimal values in the transaction identifier.  When they use the letter "E" towards the end of the ID value, it causes Excel to convert the entire value to scientific notation, which is what I need to reverse-convert.

    Thanks again for the lead!  Hope to hear from you again...happy New Year!

    James

    Was this answer helpful?

    0 comments
  5. Anonymous
    2017-01-01T19:01:43+00:00

    Can any of the members steer me in the right direction or provide some insight into locating a mathematical algorithm that will reverse the scientific notation back to its original alphanumeric format?

    Hi,

    if I understand, take a look here...

    http://www.exceldigest.com/myblog/2013/04/11/how-to-convert-numbers-between-decimal-and-hexadecimal/

    Was this answer helpful?

    0 comments