Luke Luke - 5 months ago 24
SQL Question

Looping through string in excel, replacing Chars with Alphabet

Im working with character strings in Excel, trying to replace the characters(ignoring spaces, dashes and periods) with the alphabet(in order).
These strings are in individual cells. I just want to do one cell at a time.
Ex. SG6 -099 will turn into ABC -DEF
and 3F5234-42- GA will turn into ABCDEF-GH- IJ

These strings will not be longer than 26 chars.

Is this possible?

Thanks in advance

Answer

I was able to combine two subs (one I had, the other I found). How does this work?

Function replace_Text(ByVal cel as Range) as String


Dim editText$
editText = cel.Value

Dim i&, k&
k = 1
For i = 1 To Len(editText)
    If IsLetter(Mid(editText, i, 1)) Then
        editText = WorksheetFunction.Substitute(editText, Mid(editText, i, 1), Chr$(64 + k), 1)
        k = k + 1
    End If
Next i

replace_Text = editText
End Function

Function IsLetter(strValue As String) As Boolean
' https://techniclee.wordpress.com/2010/07/21/isletter-function-for-vba/
    Dim intPos As Integer
    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 48 To 57, 65 To 90, 97 To 122
                IsLetter = True
            Case Else
                IsLetter = False
                Exit For
        End Select
    Next
End Function

In a cell, type =text_Replace(A1) where A1 is a cell with a string.