can a formula be used to make input numbers show as letters

[cardude]

example input 123 cell shows zdl
input 321 cell shows ldz
input 277 cell shows drr


1=z, 2=d, 3=l, 4=n, 5=m, 6=b, 7=r, 8=a, 9=e, 0=s


thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

If you want the result to be in the same cell that you type into then, no, a formula cannot do that. However, some vba could as described below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test. Note that I have coded this to only work in column B from row 2 downwards. I suggest you just test like that in a new worksheet to start with. Then if you cannot modify to suit your particular requirements, post back with more details.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim aSubs As Variant
  Dim i As Long
  Dim s As String
  
  aSubs = Split("1 z 2 d 3 l 4 n 5 m 6 b 7 r 8 a 9 e 0 s")
  
  Set Changed = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      s = c.Value
      If Len(s) > 0 Then
        For i = 0 To UBound(aSubs) Step 2
          s = Replace(s, aSubs(i), aSubs(i + 1), 1, -1, 1)
        Next i
        c.Value = s
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[cardude]

Welcome to the MrExcel board!

If you want the result to be in the same cell that you type into then, no, a formula cannot do that. However, some vba could as described below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test. Note that I have coded this to only work in column B from row 2 downwards. I suggest you just test like that in a new worksheet to start with. Then if you cannot modify to suit your particular requirements, post back with more details.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim aSubs As Variant
  Dim i As Long
  Dim s As String
  
  aSubs = Split("1 z 2 d 3 l 4 n 5 m 6 b 7 r 8 a 9 e 0 s")
  
  Set Changed = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      s = c.Value
      If Len(s) > 0 Then
        For i = 0 To UBound(aSubs) Step 2
          s = Replace(s, aSubs(i), aSubs(i + 1), 1, -1, 1)
        Next i
        c.Value = s
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

Works perfect...Thank you!

 

[Peter_SSs]

Works perfect...Thank you!

You are welcome.

On reflection, I would move the blue line down to where it is shown below. If the change(s) in the worksheet are unrelated to the "range of interest", there would be no need to do this 'split'.

If Not Changed Is Nothing Then
  Application.EnableEvents = False
  aSubs = Split("1 z 2 d 3 l 4 n 5 m 6 b 7 r 8 a 9 e 0 s")
  For Each c In Changed

 

[cardude]

any way to easily add this to one other column?

thanks

 

[Peter_SSs]

any way to easily add this to one other column?

Sure, what column(s) to you want it applied to? Include the original column in your answer.

Is it the the same substitution(s) in the other column(s)?

The best method may depend on whether the columns are adjacent to each other, uniformly spaced apart etc, so the more accurately you can describe what you have, where and what you are trying to achieve, the better answer you are likely to get.

 

[cardude]

Sure, what column(s) to you want it applied to? Include the original column in your answer.

Is it the the same substitution(s) in the other column(s)?

The best method may depend on whether the columns are adjacent to each other, uniformly spaced apart etc, so the more accurately you can describe what you have, where and what you are trying to achieve, the better answer you are likely to get.

i need it to work in columns M and P.. i would love to use different substitutions Letters for the numbers in column p if it does not make it much more complicated! Thanks

 

[Peter_SSs]

i would love to use different substitutions Letters for the numbers in column p

1. What are the substitutions for column M?
2. What are the substitutions for column P?
3. Are we starting in row 2 for both columns? If not details please.

 

[cardude]

1. What are the substitutions for column M?
2. What are the substitutions for column P?
3. Are we starting in row 2 for both columns? If not details please.

1=h 2=o 3=r 4=s 5=e 6=b 7=a 8=c 9=k 0=x for column M

1=a 2=w 3=e 4=s 5=t 6=r 7=u 8=c 9=k 0=x for coulumn p

starting in row 2 is perfect thanks in advance!

 

[Peter_SSs]

For two columns its probably simplest just to double-up the original code but with the alternate substitutions, so try

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim aSubs As Variant
  Dim i As Long
  Dim s As String
  
  'Column M
  Set Changed = Intersect(Target, Columns("M"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    aSubs = Split("1 h 2 o 3 r 4 s 5 e 6 b 7 a 8 c 9 k 0 x")
    For Each c In Changed
      s = c.Value
      If Len(s) > 0 Then
        For i = 0 To UBound(aSubs) Step 2
          s = Replace(s, aSubs(i), aSubs(i + 1), 1, -1, 1)
        Next i
        c.Value = s
      End If
    Next c
    Application.EnableEvents = True
  End If
  
  'Column P
  Set Changed = Intersect(Target, Columns("P"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    aSubs = Split("1 a 2 w 3 e 4 s 5 t 6 r 7 u 8 c 9 k 0 x")
    For Each c In Changed
      s = c.Value
      If Len(s) > 0 Then
        For i = 0 To UBound(aSubs) Step 2
          s = Replace(s, aSubs(i), aSubs(i + 1), 1, -1, 1)
        Next i
        c.Value = s
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub