Excel VBA: search a string to find the first non-text character

excel, regex, vba

Solution

The regexp below looks to remove from the first non `A-Z` character.

Function StrChange(strIn As String) As String
Dim objRegEx As Object

Set objRegEx = CreateObject("vbscript.regexp")
With objRegEx
    .ignorecase = True
    .Pattern = "^([a-z]+)([^a-z].*)"
    .Global = True
     StrChange = .Replace(strIn, "$1")
End With
End Function

Problem

Cells contain a mixture of characters within a string, such as: Abcdef_8765 QWERTY3_JJHH Xyz9mnop I need to find the first non `A-Za-z` character so that I can strip out the subsequent remainder of the string. So the results would be: Abcdef QWERTY Xyz I know how to do this if I know exactly what character I'm looking for, but I'm not intuitively grasping how to find ANY character other than `A-Za-z`. Btw, this is intended to be used within a vba solution. ==================== EDIT: I've had success with the following... ``` a = "abc123" b = Len(a) For x = 1 To b c = (Mid(a, x, 1) Like "[a-zA-Z]") If c = False Then d = Left(a, x - 1) Exit Sub End If Next x ``` Have I stumbled upon a suitable solution, or is this destined to break? I ask only because I look at Doug Glancy's solution and it seems much more substantial. (btw, I have not yet tested Doug's solution)

Original source