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
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
A1 is a cell with a string.