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

CodePudding user response：

You can use

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

See the

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:

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