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

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

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

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

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