Alphanumeric to Numeric

[dleitch70]

I'm looking for help with converting an alphanumeric number (ex. ug7v899j) to a numeric number? I would appreciate any suggestions. So far I've not been able to find anything on the forum.

 

[shaowu459]

What is the converting rule? Can you show us a sample?

 

[sandy666]

with Power Query

AN	N
ug7v899j	7899

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TS = Table.AddColumn(Source, "N", each Text.Select([AN],{"0".."9"}))
in
    TS

HTH

 

[dleitch70]

What is the converting rule? Can you show us a sample?

All of the alphanumeric values are 8 characters long. They obviously consist of letters and characters. All the letters are lower case. I'm not exactly sure what you mean by converting rule. I looking for a way to convert ug7v899j to something like 84788995.

Open to any and all suggestions.

 

[shaowu459]

ug7v899j
84788995

I mean why convert j to5, u to 8, g to 4, v to 8? why u and v both convet to 8?

 

[Joe4]

I looking for a way to convert ug7v899j to something like 84788995.

How exactly does "ug7v899j" translate to "84788995"?
Somehow, you seem to by determining that u=8, g=4, v=8, and j=5.
What is that "rule" for determining that?

 

[dleitch70]

with Power Query

AN	N
ug7v899j	7899

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TS = Table.AddColumn(Source, "N", each Text.Select([AN],{"0".."9"}))
in
    TS

HTH

Hi Sandy, Thanks but I'm looking for something to convert the alphanumeric to a numeric, not just to separate the numeric from the alpha characters.

 

[dleitch70]

ug7v899j
84788995

I mean why convert j to5, u to 8, g to 4, v to 8? why u and v both convet to 8?

I used an online Alpha phrase to phone number as the example. I want to stress I don't think that's the way it should convert. I'm looking for any and all suggestions on how it should be done correctly. The example was just to emphasize that I need the alpha characters to be converted to numbers.

If ug7v899j should convert to 21772289910, that's fine with me. I just need to know how to do that in excel because I have 158,000 that need to be converted.

Thanks!

 

[shaowu459]

If you have Excel 365, that will be easy to do:

工作簿4
	A	B	C	D
1	ug7v899j	21772289910
2
3	ug7v899j should convert to 2172289910
4
Sheet1

Cell Formulas
Range	Formula
B1	B1	=CONCAT(IFERROR(MATCH(MID(A1,ROW($1:$8),1),CHAR(ROW($65:$90)),),MID(A1,ROW($1:$8),1)))

 

[shaowu459]

With hleper column:

工作簿4
	A	B	C	D	E	F	G	H	I	J
1	ug7v899j	21	7	7	22	8	9	9	10	21772289910
2
3
Sheet1

Cell Formulas
Range	Formula
B1:I1	B1	=IFERROR(--MID($A1,COLUMN(A1),1),CODE(MID($A1,COLUMN(A1),1))-96)
J1	J1	=B1&C1&D1&E1&F1&G1&H1&I1