Convert Alphanumeric String To Numbers

Hello everyone. I have an Excel 2016 .xlsx file with just over 37,000 rows, and 20 columns. One of those columns has a unique 4 character code, that I need to turn into a unique numeric-only code (up to 7 digits max). Some examples of this code are below:

9KDB
4JJJ
H1YA
04HA
RYUA

I was thinking if there is a way to convert a letter into a digit using the key below, it should work out to where all 37,000 entries are still unique:

A - 0
B - 1
C - 2
D - 3
E - 4
F - 5
G - 6
H - 7
I - 8
J - 9
K - 10
L - 11
M - 12
N - 13
O - 14
P - 15
Q - 16
R - 17
S - 18
T - 19
U - 20
V - 21
W - 22
X - 23
Y - 24
Z - 25

I would truly appreciate any help with this. Thank you!

Please Login or Register  to view this content.

Paste this code into a macro module.

Then the formula =ReCode(a1) will translate the number in A1 for you.

Please Login or Register  to view this content.

Bit ugly, but

Formula:

Please Login or Register  to view this content.

Hello mightymo77. Welcome to the forum.

Try this and see if it does the job. Your sample outputs as no more than 6 digits. See how it performs with larger "numbers".

Treat those as base 36 numbers. Covert to decimals with source data in column A this in B1

Formula:

Please Login or Register  to view this content.

Then to convert back this in C1

Formula:

Please Login or Register  to view this content.

	A	B	C
1	0KCB	26363	0KCB
2	7XJK	370064	7XJK
3	LB8A	994330	LB8A
4	9BGA	434746	9BGA
5	D9EA	618706	D9EA

Based on the VBA UDF from mehmetcik

Formula:

Please Login or Register  to view this content.

@FR: ooohhhhh ... I like that! Why not combine the two?

Formula:

Please Login or Register  to view this content.

That is so neat.

Thank you, and yes combining the two a cool idea. I learned about the DECIMAL base 36 trick from Lori over at XOR LX's site. She used it to convert A1 cell addresses to R1C1.

Wow thank you guys very much! I really do appreciate it. Which one of these should I use, I see a bunch of different options. Sorry, once I learn I will have it :D

You're welcome.

Personally, I’d go with post #7 … the combined version of FlameRetired's solution. You should test all of the solutions with a wider range of codes to see how they all cope.

I wouldn't go with mehmetcik's UDF (nothing personal ) unless you are using VBA anyway.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

My bad. I was thinking it was a two stage process. But it's not. One is converting it (to something). The other is converting it back. By combining the formulae, it just gives you back what you started with … which is a good sign maybe but not what you need.

That said, FR's solution doesn't convert by the rules you outlined. You ok with that?

Please Login or Register  to view this content.

Inserts formula into column C

VBA to do Base 36 calculation:

Please Login or Register  to view this content.

Results in column D

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Administrative Note:

Welcome to the forum.

Unfortunately, it has come to our attention you have violated Rule #8 of our Forum RULES:

Don't private message, visitor message or email Excel (or Access, Word, etc.) questions to moderators or other members.

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Breaking this rule is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offense could lead to a permanent ban, so do take this caution to heart.

Post your question in a public thread and our many contributors will come to your assistance, especially if the title is accurate (see Rule #1) and you include a sample desensitized workbook that makes it easy for others to try and help.

I've responded to your PM with this in mind.

Please keep related questions posted in this thread.

And thank you for the feedback and added rep.