SOLVED

Highlighted
New Contributor

# Excel -- Converting letters to number

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?

27 Replies
Highlighted

# Re: Excel -- Converting letters to number

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.

Highlighted

# Re: Excel -- Converting letters to number

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

LOOKUP with SORT looks great.

Highlighted

# Re: Excel -- Converting letters to number

Tried that with INDEX, works as

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

Highlighted

# Re: Excel -- Converting letters to number

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.

Highlighted

# Re: Excel -- Converting letters to number

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)))``````
Highlighted

# Re: Excel -- Converting letters to number

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?

Highlighted

# Re: Excel -- Converting letters to number

@JFP-475

Hi,

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

Hope that helps

Highlighted

# Re: Excel -- Converting letters to number

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