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 bc = (Mid(a, x, 1) Like "[a-zA-Z]")If c = False Thend = Left(a, x - 1)Exit SubEnd IfNext 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)

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
``````

Here is a simple way which doesn't use RegEx. I am deliberately not using RegEx as the other two answer are based on RegEx. `RegEx` is definitely faster but this is almost equally fast. The difference in speed is almost negligible.

``````Function GetWord(Rng As Range)
Dim i As Long, pos As Long

For i = 1 To Len(Rng.Value)
Select Case Asc(Mid(Rng.Value, i, 1))
Case 65 To 90, 97 To 122
Case Else: pos = i: Exit For
End Select
Next i

GetWord = Left(Rng.Value, pos - 1)
End Function
``````

Usage:

``````=GetWord(A1)
``````

EDIT:

Followup from comments. Fine tuned the code (Courtesy @brettdj) .

``````Function GetWord(Rng As Range)
Dim i As Long, pos As Long
Dim sString As String

sString = UCase\$(Rng.Value)

For i = 1 To Len(sString)
Select Case Asc(Mid\$(sString, i, 1))
Case 65 To 90
Case Else: pos = i: Exit For
End Select
Next i

GetWord = Left(Rng.Value, pos - 1)
End Function
``````

More Followup.

Here is something which I had never tried before. I did an actual test of my code vs RegXp and I was surprised to see my code was faster than `RegXp` which I had not anticipated.

I tested it on 10k cells and each cell had a string of 2256 of length

The string that I put in Cell `A1:A10000` is

`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5Rout`

Next I ran this test

You can use a simple regular expression to specify a numeral followed by anything and use this function to replace anything that matches that pattern:

``````Function Regex_Replace(strOriginal As String, strPattern As String, strReplacement, varIgnoreCase As Boolean) As String
Dim objRegExp As Object

Set objRegExp = CreateObject("vbscript.regexp")
With objRegExp
.Pattern = strPattern
.IgnoreCase = varIgnoreCase
.Global = True
End With

Regex_Replace = objRegExp.Replace(strOriginal, strReplacement)
Set objRegExp = Nothing
End Function
``````

You'd call it like this:

``````Sub DeleteAfterNums()
Dim cell As Excel.Range

'Change "Selection" to your range
For Each cell In Selection
'"\d.+" is a numeral and whatever follows it
cell.Value = Regex_Replace(cell.Value, "\d.+", "", True)
Next cell
End Sub
``````

Here is a lightweight and fast method that avoids regex/reference additions, thus helping with overhead and transportability should that be an advantage.

``````Public Function GetText(xValue As String) As Variant

For GetText = 1 To Len(xValue)
If UCase(Mid(xValue, GetText, 1)) Like "[!A-Z]" Then GetText = Left(xValue, GetText - 1): Exit Function
Next

GetText = xValue

End Function
``````

This is then called by using GetText("Submission String") from vba or prepended with a "=" from within a cell formula.

Top