Remove numbers from end of string if count of numbers(characters) > 8

user_15586050 0
Viewed 128

enter image description here

I need to remove numbers from end of string if count of numbers(characters) > 8
I have used the below functions , but it remove all numbers from the string.
So, How this function can be modified to add a condition if count of numbers(characters) > 8
In advance, grateful for any helpful comments and answers. 

Option Explicit
 
Function StripNumber(stdText As String)
  Dim str As String, i As Integer
 
    stdText = Trim(stdText)
 
    For i = 1 To Len(stdText)
        If Not IsNumeric(Mid(stdText, i, 1)) Then
            str = str & Mid(stdText, i, 1)
        End If
    Next i
        StripNumber = str ' * 1
End Function
 
Function Remove_Number(Text As String) As String
    With CreateObject("VBScript.RegExp")
      .Global = True
      .Pattern = "[0-9]"
      Remove_Number = .Replace(Text, "")
    End With
End Function
vba excel regex
2 Answers

You can use

\d{8,}(?=\.\w+$)
\d{8,}(?=\.[^.]+$)

See the regex demo. If there must be at least 9 digits, replace 8 with 9.

Details:

  • \d{8,} - eight or more digits
  • (?=\.\w+$) - that are immediately followed with a . and one or more word chars and then end of string must follow
  • (?=\.[^.]+$) - the eight or more digits must be immediately followed with a . char and then one or more chars other than a . char till the end of string.
user_38329700

If you have access to the newest functions you can avoid VBA alltogether:

enter image description here

Formula in B2:

=LET(X,TEXTBEFORE(A2,".",-1),Y,TEXTAFTER(A2,X),Z,TEXTAFTER(CONCAT(".",IFERROR(--MID(X,SEQUENCE(LEN(X)),1),".")),".",-1),IF(LEN(Z)>8,SUBSTITUTE(A2,Z&Y,Y),A2))

Or, if there are no leading zeros in these numbers:

=LET(X,TEXTBEFORE(A2,".",-1),Y,TEXTAFTER(A2,X),Z,MAX(IFERROR(--MID(X,SEQUENCE(LEN(X)),LEN(X)),"")),IF(LEN(Z)>8,SUBSTITUTE(A2,Z&Y,Y),A2))

Or; a spilled array:

enter image description here

Formula in B2:

=BYROW(A2:A6,LAMBDA(a,LET(X,TEXTBEFORE(a,".",-1),Y,TEXTAFTER(a,X),Z,TEXTAFTER(CONCAT(".",IFERROR(--MID(X,SEQUENCE(LEN(X)),1),".")),".",-1),IF(LEN(Z)>8,SUBSTITUTE(a,Z&Y,Y),a))))

Or:

=BYROW(A2:A6,LAMBDA(a,LET(X,TEXTBEFORE(a,".",-1),Y,TEXTAFTER(a,X),Z,MAX(IFERROR(--MID(X,SEQUENCE(LEN(X)),LEN(X)),"")),IF(LEN(Z)>8,SUBSTITUTE(a,Z&Y,Y),a))))
user_97581940
Related