Cells contain a mixture of characters within a string, such as:
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:
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
Btw, this is intended to be used within a vba solution.
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)
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
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
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
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
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.