Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace or substitute a letter for a specific number
Say A=65, B=66, C=76.. Z=90
I want to convert the alfanumeric value "AB25" into 656625... Cant find the way to replace/substitute A for 65 and B for 66 followed by 25 to make a single number... Is this possible??? THANX |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace or substitute a letter for a specific number
Try this macro you have to open the macro editor, insert a module and paste
it in and then must =convertnr(A1) in B1 if the number is in A1. Function convertnr(r As Range) As String ' could change it to string and adjust the ucase line to r only. Dim s_converted As String Dim s_original As String Dim s As String Dim i As Long s_original = UCase(r.Value) For i = 1 To Len(s_original) s = Mid(s_original, i, 1) If IsNumeric(s) Then s_converted = s_converted & s Else s_converted = s_converted & Asc(s) End If Next i convertnr = s_converted End Function -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "El Criollo" wrote: Say A=65, B=66, C=76.. Z=90 I want to convert the alfanumeric value "AB25" into 656625... Cant find the way to replace/substitute A for 65 and B for 66 followed by 25 to make a single number... Is this possible??? THANX |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace or substitute a letter for a specific number
Works perfect! Great!
Im reading the code... barely understant what it is doing... but it works... I just had to make a previous step on another cell to remove spaces and "-" by substitution... (That's easy!) But if I want that formula to work on another book or other computer... i have to copy it again?? well, yes on another PC, but on a new file as well? Or the formula will become part of the Excell formulas ? "Martin Fishlock" wrote: Try this macro you have to open the macro editor, insert a module and paste it in and then must =convertnr(A1) in B1 if the number is in A1. Function convertnr(r As Range) As String ' could change it to string and adjust the ucase line to r only. Dim s_converted As String Dim s_original As String Dim s As String Dim i As Long s_original = UCase(r.Value) For i = 1 To Len(s_original) s = Mid(s_original, i, 1) If IsNumeric(s) Then s_converted = s_converted & s Else s_converted = s_converted & Asc(s) End If Next i convertnr = s_converted End Function -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "El Criollo" wrote: Say A=65, B=66, C=76.. Z=90 I want to convert the alfanumeric value "AB25" into 656625... Cant find the way to replace/substitute A for 65 and B for 66 followed by 25 to make a single number... Is this possible??? THANX |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace or substitute a letter for a specific number
=CODE(LEFT(A6,1))&CODE(MID(A6,2,1))&MID(A6,3,999)
-- David Biddulph "El Criollo" wrote in message ... Say A=65, B=66, C=76.. Z=90 I want to convert the alfanumeric value "AB25" into 656625... Cant find the way to replace/substitute A for 65 and B for 66 followed by 25 to make a single number... Is this possible??? THANX |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace or substitute a letter for a specific number
Hey david...
That doesnt work... it has to change only the letters... if i enter no letter it is changing the numbers also... And, in case there is another letter say in position6 of the number, it doent change it either... but the macro is doing allright... TNX "David Biddulph" wrote: =CODE(LEFT(A6,1))&CODE(MID(A6,2,1))&MID(A6,3,999) -- David Biddulph "El Criollo" wrote in message ... Say A=65, B=66, C=76.. Z=90 I want to convert the alfanumeric value "AB25" into 656625... Cant find the way to replace/substitute A for 65 and B for 66 followed by 25 to make a single number... Is this possible??? THANX |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of uniques starting with a given letter? | Excel Discussion (Misc queries) | |||
Formula for cells that contain a specific letter | Excel Worksheet Functions | |||
linking a cell with a specific letter value to a cell with a formu | Excel Worksheet Functions | |||
Assigning a number value to a letter? | Excel Discussion (Misc queries) | |||
how do I find an average number of specific words in a column | New Users to Excel |