Forum Discussion
Excel -- Converting letters to number
Hi Jason,
If you familiar with VBA in Excel you can create a custom function to do this and use it in the worksheet.
This is my suggested function:
Option Compare Text
Function LettersToNumbers(text As String) As Integer
Dim i As Integer
Dim result As String
For i = 1 To Len(text)
Select Case Mid(text, i, 1)
Case "a"
result = result & 1
Case "b"
result = result & 2
Case "c"
result = result & 3
Case "d"
result = result & 4
Case "e"
result = result & 5
Case "f"
result = result & 6
Case "g"
result = result & 7
Case "h"
result = result & 8
Case "i"
result = result & 9
Case "j"
result = result & 0
End Select
Next
LettersToNumbers = result
End FunctionPlease follow this https://www.excel-easy.com/vba/examples/user-defined-function.html to figure out how to insert this function into your own workbook.
Also, you can place it in the Personal Macro Workbook so that the function will be available in all open workbook and this is described here in this https://www.myonlinetraininghub.com/moving-vba-code-and-forms-into-personal-xlsb.
However, I've inserted it into the attached workbook.
Hope that helps
Yes I did it in same manner, when I open your file it is read only where I can see it was correct the moment I click on edit the file it vanishes
Sorry. I missed this trick with N(IF1,..). Please check
=TEXTJOIN("",1,
VLOOKUP(N(IF(1,
--MID(D2,ROW($A$1:INDEX($A$1:$A$18,LEN(D2))),1)
)),
$A$2:$B$11,2,0
)
)
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)))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.
Tried that with INDEX, works as
=CONCAT( INDEX(SORT(MappingRange), MID(Number,SEQUENCE(LEN(Number)),1)+1, 2) )Peter, you are right. I simply forgot about coercion since in DA formula works, after that add correction.
LOOKUP with SORT looks great.
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.
I sometimes use LOOKUP as an alternative to INDEX when coercion is required.
= CONCAT( LOOKUP( VALUE(MID(number,k,1)), digits, characters ) )
The reverse decoding and I do not have to convert text to numbers
= CONCAT( LOOKUP( MID(text, k, 1), array ) )
but the elephant in that room is that I have had to sort the lookup data
It is OK for me to use 'array'
= SORT( IF( {0,1}, digits, characters ) )
but most users would be better off with a manual sort using a helper range.
Great it is working ! but when I tried it in different cells I don't know why it is not working I changed all the reference cell addresses.. It is working only at A1
