Clever Geek Handbook

Remove the last character from the first word in a string

I am using RegEx to find a string

If RegExp.test(Cel.Value) Then
    Debug.Print Left$(Cel.Value, Len(Cel.Value) - 1)
End If

This is my data

3250A WEST SEM
110055K KEALY RD
804B WEST AMERICA
804 EAST AMERICA

The result should be

3250 WEST SEM
110055 KEALY RD
804 WEST AMERICA
804 EAST AMERICA

What's the best way to remove the last character?

+3


source to share


5 answers


You can try this:

^(\d+)[a-zA-Z]*(\s+.*)$

And replace with this:



$1$2

Demo

+4


source


You can use this regex:

(\d+)(\w{1})(\s.+)
  • (\d+)

    - capture one or more digits
  • (\w{1})

    - capture one letter you want to remove - if you have 123XX SOMEWHERE examples, then to capture XX you would use (\w+)

    or (\w{1,2})

    if you think there will be 1 or 2 characters following the number.
  • (\s.+)

    - grab a space followed by something

If you want to replace, you just want to remove the second match group ( (\w{1})

) and join the first and third - from here $1$3

in the Replace

function below:

strReplaced = objRegex.Replace(CStr(varTest), "$1$3")


Sample VBA Code

Option Explicit

Sub Test()

    Dim objRegex As Object
    Dim varTests As Variant
    Dim varTest As Variant
    Dim strPattern As String
    Dim strReplaced As String

    varTests = Array("3250A WEST SEM", "110055K KEALY RD", "804B WEST AMERICA")
    strPattern = "(\d+)(\w{1})(\s.+)"

    Set objRegex = CreateObject("VBScript.Regexp")
    objRegex.Pattern = strPattern

    For Each varTest In varTests
        strReplaced = objRegex.Replace(CStr(varTest), "$1$3")
        MsgBox strReplaced
    Next varTest

End Sub

Fancy regex diagram:

enter image description here

+4


source


RegEx Script - If you can find <some numbers> <single letter> <single space> at the beginning of a line , then change the pattern to <single letter> <single space> and replace it with single space.

Option Explicit

Sub stripAlphaSuffix()
    Dim i As Long, regex As Object

    Set regex = CreateObject("VBScript.RegExp")

    With Worksheets("Sheet1")
        For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            With regex
                .Global = False
                .MultiLine = False
                .IgnoreCase = False
                .Pattern = "^[0-9A-Z ]{3,8}"
            End With
            If regex.Test(.Cells(i, "A").Value2) Then
                regex.Pattern = "[A-Z ]{2}"
                .Cells(i, "A") = regex.Replace(.Cells(i, "A").Value2, Chr(32))
            End If
        Next i
    End With
End Sub
+1


source


just to throw a non-RegEx solution:

Dim cel As Range
Dim word As String

For Each cel In Range("A1", Cells(Rows.Count, 1).End(xlUp))
    word = Split(cel, " ")(0)
    cel.Value = Replace(cel.Value, word, Left(word, Len(word) - 1))
Next
+1


source


As VAL

removes non-zero numbers from an alphanumeric string starting with a number, then you can try this (which doesn't suggest there is always a character to be replaced)

VAL("3250A")

= 3250

It would be more elegant if there was no need to add the rest of the lines :)

Dim StrIn As String
Dim X
StrIn = "3250A WEST SEM"
X = Split(StrIn, Chr(32))
MsgBox Val(StrIn) & Right$(StrIn, Len(StrIn) - Len(X(0)))
+1


source






More articles:


All Articles