Search & Replace InString

Hi all,

I'm having somewhat of a difficult time trying to put some code together to
search for a string of characters matching a specific arrangement.

The specific arrange would be any numerical value followed by an alpha character.
such as: (1abc,12abc,123abc, 1234abc, 1234nabc....) n= any numeral of any length
abc = any alpha string of any length.

I'd would like to start the search from the LEFT(1abc,1), and for the only the
first case found,

the a space " " needs to be put between the numeric and alpha characters.
such as: (1 abc,12 abc,123 abc, 1234 abc, 1234 nabc....)
I don't know if these would be easier using a "case select" code
or something like

Private Sub CommandButton2_Click()
'For a text box imput

Dim testLChar as string
'Dim testMChar as string
Dim testMChar1 as string
Dim testMChar2 as string
Dim N As Integer
With Me.Txt_LOCATION
N = 1
testLChar = Left(.Value, N)
testMChar1 = Mid(.Value, N)
Do Until (testMChar1 Like "[0-9|A-Z]") = (testMChar2 Like "[A-Z_]")
If (testLChar Like "[0-9-A-Z]") Then
testMChar2 = Mid(.Value, N)
N = N + 1
If (testMChar2 Like "[A-Z]") Then
testMChar2 = " " & testMChar2
End If
End If
Loop
End With
end sub

...above code not working.

Any help is appreciated.

Thanks,

BDB

You could try logic like this.

Sub Test()
TestString = "1234abcd"
For N = 1 To Len(TestString)
    If IsNumeric(Left(TestString, N)) = False Then
        NewString = Left(TestString, N - 1) & " " & Right(TestString, Len(TestString) - N + 1)
        Exit For
    End If
Next N
End Sub

Formula approach

Function InsertGap(MyString As String) As String
    Dim Symbol As Variant
    Dim j As Long
    On Error Resume Next
    With WorksheetFunction
        For j = 1 To Len(MyString)
            Symbol = Symbol & Mid(MyString, j, 1)
            If Not IsNumeric(Mid(MyString, j, 1)) Then
                Exit For
            End If
        Next j
    InsertGap = .Replace(MyString, Len(Symbol), 0, " ")
    End With
End Function

Thanks guys, but something is still not right.

mrice, The code as is did not change anything. I've been trying to figure out what's not right with it. Yet to correct.

Here's another attempt which does nothing.

For N = 1 To Len(TestString)
    If IsNumeric(Left(TestString, N)) = True Then
       'TestString = Right(TestString,N)
        NewString = (Left(TestString, N))
         If InStr(1, NewString, NewString Like "[1-9],[A-Z]") > 0 Then
        ' If NewString Like "[1-9],[A-Z]" Then
       ' If INST("*",(NewString) = False Then
        TestString = Left(NewString, N - 1) & " " & Right(NewString, Len(NewString) - N) ' I Think something is not right with the right half of this line starting @ &Right(
        Exit For
    End If
    End If
    Next N

Forum Guru, I'm not real sure how I would apply the formula to my code.
If you can supply some code that I could use, then I'd be more than happy to try it.

BDB

Hello BDB,

This is quite easy to do using Regular Expressions. Here is an example.

Sub FindReplace()

  Dim RegExp As Object
  Dim Text As String
  
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Global = True
    RegExp.Pattern = "(\d+)(\w+)"
    
      Text = "1abc,12abc,123abc, 1234abc, 1234nabc"
      
      MsgBox RegExp.Replace(Text, "$1" & " " & "$2")
    
    Set RegExp = Nothing
    
End Sub

Hello BDB,

Do you want the modified text from the TextBox to be transfered to a worksheet cell or just displayed in the TextBox?