Aug 23 2018 02:15 PM
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?
Aug 27 2019 10:51 AM
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.
Aug 27 2019 11:25 AM
Peter, you are right. I simply forgot about coercion since in DA formula works, after that add correction.
LOOKUP with SORT looks great.
Aug 27 2019 01:39 PM
Tried that with INDEX, works as
=CONCAT(
INDEX(SORT(MappingRange),
MID(Number,SEQUENCE(LEN(Number)),1)+1,
2)
)
Aug 28 2019 03:06 AM
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.
Aug 28 2019 02:49 PM
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)))
Feb 06 2020 06:07 AM - edited Feb 06 2020 06:35 AM
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?
Feb 08 2020 07:45 AM
Apr 02 2020 06:48 AM
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
Mar 14 2021 06:38 PM
Apr 09 2021 04:32 AM
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 |
Apr 09 2021 04:34 AM
Apr 09 2021 05:36 AM
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
Oct 14 2021 03:42 PM
Oct 18 2021 04:33 AM
Oct 18 2021 04:44 AM
Why do you have Z=7 and Z=9?