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.What is the converting rule? Can you show us a sample?
How exactly does "ug7v899j" translate to "84788995"?I looking for a way to convert ug7v899j to something like 84788995.
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.with Power Query
AN N ug7v899j 7899
HTHRich (BB code):let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], TS = Table.AddColumn(Source, "N", each Text.Select([AN],{"0".."9"})) in TS
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.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?
工作簿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))) |