Excel -- Converting letters to number

[Jason Summer]

Hi, all.  Hoping I could find some help:  I need to convert a string of letters into numbers, and vice versa.  The set-up: the numbers 1,2,3,4,5,6,7,8,9,0 correspond to letters a,b,c,d,e,f,g,h,i,j.  Example: If a user enters hajg in the cell, the formula (or macro) produces 8107.  Or dibe produces 4925.

I can have two formulas, so the conversion doesn't have to go both ways in one formula.  I just need to do both.

Any ideas out there?

[#2]

@Bhushan_Shiurkar 

For your ranges the formula will be

=TEXTJOIN("",1,
    VLOOKUP(N(IF(1,
        --MID(F4,ROW($A$1:INDEX($A$1:$A$20,LEN(F4))),1)
            )),
        $C$4:$D$13,2,0
    )
)

The point is that the part of the formula

ROW($A$1:INDEX($A$1:$A$20,LEN(F4)))

which returns sequential integers from 1 to number of characters in the number, shall always start from row #1 (column doesn't matter) to return sequence like {1,2,3}.

Alternatively you may use here

=ROW(INDIRECT("1:"& LEN(F4)))

to avoid side effect when rows are inserted.

[#3]

@Peter Bartholomew 

Peter, you are right. I simply forgot about coercion since in DA formula works, after that add correction.

LOOKUP with SORT looks great.

[#4]

@Peter Bartholomew 

Tried that with INDEX, works as

=CONCAT(
  INDEX(SORT(MappingRange),
    MID(Number,SEQUENCE(LEN(Number)),1)+1,
    2)
)

[#5]

@Sergei Baklan 

Agreed, it works well. 

Do you know of any documentation on the behaviour of INDEX when it has array row and column parameters?  Coercion was always something of a black art but now the rules of engagement appear to have changed.  The truncation behaviour seems to depend upon the manner in which the formula is built e.g. a zero column parameter will fail but the row array SEQUENCE(1, n) may work as the parameter.

I tend to view filtering and sorting as presentational devices associated with lists.  I think it will take a while to fully explore the extent to which which the new functions FILTER and SORT can contribute to normal array calculation.

[#6]

@Peter Bartholomew 

Peter - nope, I didn't see such documentation. That's usually play with this / play with that approach.

By the way, checked how it works with newly introduced functions. As expected.

=CONCAT(XLOOKUP(--MID(number,SEQUENCE(LEN(number)),1),Input,Output))

=CONCAT(INDEX(Output,XMATCH(--MID(number,SEQUENCE(LEN(number)),1),Input)))

[#7]

I realize this thread is a bit old but..

I'm getting an error message with Haytham's formula any time my string of letters is greater than 5. I'm basically trying to create ID numbers for employees based on their first and last names.

James converts to 11451 -> correct for the code as written.
George grants an error message

any idea how to fix?

[#8]

@JFP-475

Hi,

I've updated the code in the attached file to fix this issue.

Hope that helps

[#9]

@Haytham Amairah 

Hi,

I realize this thread is a bit old but… would like to make a joke with excel. Attached is a file where in cell A1 there is a date in four digit and in any cell where if Easter(A1) is written then appears some words. So I want  "Buona Pasqua a tutto il forum" has to appear.

Thanks a lot for your attention. Regards

Ricky

XP + Office2003

[#10]

@Haytham Amairah 

Copied the code you suggested. I keep getting a #NAME? response. Suggestions?

[#11]

INPUT	INPUT 1	INPUT 2	INPUT 3	OUTPUT
D	T			1
N				2
M				3
R				4
L				5
J	G	SH	CH	6
K	C	Q
F	V	TH		8
P	B			9
Z	S			0

[#12]

INPUT INPUT 1 INPUT 2 INPUT 3 OUTPUT
D T 1
N 2
M 3
R 4
L 5
J G SH CH 6
K C Q 7
F V TH 8
P B 9
Z S (S sounding) C 0


can you share vba for above, Thanks

[#13]

@Nitish_verma 

Try this:

Function LettersToNumbers(text As String) As String
    Dim i As Long
    Dim result As String

    For i = 1 To Len(text)
        Select Case UCase(Mid(text, i, 1))
            Case "D"
                result = result & 1
            Case "T"
                If UCase(Mid(text, i + 1, 1)) = "H" Then
                    result = result & 8
                    i = i + 1
                Else
                    result = result & 1
                End If
            Case "N"
                result = result & 2
            Case "M"
                result = result & 3
            Case "R"
                result = result & 4
            Case "L"
                result = result & 5
            Case "C"
                If UCase(Mid(text, i + 1, 1)) = "H" Then
                    result = result & 6
                    i = i + 1
                Else
                    result = result & 7
                End If
            Case "S"
                If UCase(Mid(text, i + 1, 1)) = "H" Then
                    result = result & 6
                    i = i + 1
                Else
                    result = result & 0
                End If
            Case "J", "G"
                result = result & 6
            Case "K", "Q"
                result = result & 7
            Case "F", "V"
                result = result & 8
            Case "P", "B"
                result = result & 9
            Case "Z"
                result = result & 0
            End Select
    Next i

    LettersToNumbers = result
End Function

[#14]

Hello, I was wondering if you could help me. If I wanted to do this with letters to different letters what would I need to change to make this work? Thank you.

[#15]

@a_heb2275 

See your new discussion 

[#16]

HI all. Hoping I could find some help: I need to convert a string of letters into numbers.
B=1,A=2,E=3,D=4,L=5,C=6,Z=7,Y=8,Z=9,X=0.
When I input a cell in excel sheet BAED Then result show 1234.

For example
BDCX 1460
BAED 1234
BBBB 1111
XZCL 0965

pls help

[#17]

@shohag15 

Why do you have Z=7 and Z=9?