CODEWITHSUNDEEP
stringexcelvba
Ben Hickleton
Ben Hickleton

Reputation: 13

Excel VBA insert character between number and letter

I would like some VBA code that would allow me to detect if a string contains any instances of a number followed by a letter and then insert a new character between them. For example:

User enters the following string:

4x^2+3x

Function returns:

4*x^2+3*x

Thanks in advance.

Edit: Thanks for the advice guys, I think I have it working but I'd like to see if you can improve what I've got:

Sub insertStr()
    On Error Resume Next
    Dim originalString As String
    Dim newLeft As String
    Dim newRight As String
    originalString = Cells(1, 1).Value
Repeat:
    For i = 1 To Len(originalString)
        If IsNumeric(Mid(originalString, i, 1)) = True Then
            Select Case Asc(Mid(originalString, i + 1, 1))
                Case 65 To 90, 97 To 122
                    newLeft = Left(originalString, i)
                    newRight = Right(originalString, Len(originalString) - i)
                    originalString = newLeft & "*" & newRight
                    GoTo Repeat
                Case Else
                    GoTo Nexti
            End Select
        End If
Nexti:
    Next i
End Sub

Upvotes: 1

Views: 2139

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

And just to show how it might be done using Regular expressions, and also allowing you to specify any particular character to insert:

Option Explicit
Function InsertChar(S As String, Insert As String) As String
    Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "(\d)(?=[A-Za-z])"
    InsertChar = .Replace(S, "$1" & Insert)
End With
End Function

The pattern is interpreted as

  • \d Find any number and capture it
  • (?=[A-Za-z]) that is followed by a letter

And the replacement is

  • $1 return the capturing group
  • & concatenated with
  • Insert (the string to be inserted)

Upvotes: 4

Gary's Student
Gary's Student

Reputation: 96753

Following Ron's suggestion:

Public Function InsertStar(sIn As String) As String
    Dim L As Long, temp As String, CH As String
    L = Len(sIn)
    temp = Left(sIn, 1)
    For i = 2 To L
        CH = Mid(sIn, i, 1)
        If IsLetter(CH) And IsNumeric(Right(temp, 1)) Then
           temp = temp & "*"
        End If
        temp = temp & CH
    Next i
    InsertStar = temp
End Function

Public Function IsLetter(sIn As String) As Boolean
    If sIn Like "[a-zA-Z]" Then
        IsLetter = True
    Else
        IsLetter = False
    End If
End Function

Upvotes: 0

Related Questions