need help adding a character after a series of numbers

[maurabwade]

Hi. I need to make a macro that says "any time you find four numbers followed by a space, add a tab afterwards".

for example
5623
6683
2280

I want it to add a tab after each of those.

Any idea how I can do this?

[Siddharth Rout]

Welcome to the forums maurabwade

I can give you an answer but I would like you to try it first. Recording a macro and then amend it to suit your needs. You might want to see this on how to record a macro?

If you are still stuck then show us the code that you tried and we will take it from there?

[maurabwade]

Oh I already recorded a replace macro, but that didn't do me any good. This is the code to that:

Sub digits()
'
' digits Macro
'
'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^#^#^#^#"
.Replacement.Text = "^#^#^#^#^t"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

It just gave me a runtime error.

I guess a replace command isn't really appropriate because I don't want to replace the same string of numbers each time, so I'm not sure what to do next.

[vbfbryce]

Where are you looking for the 4 numbers followed by a space? In a specific row or column? Or anywhere on the worksheet(s)?

[vbfbryce]

For this example, I put some strings in cells A1 through A7. This code will check to see if there are 4 consecutive numbers followed by a space anywhere within the string in each cell, then add a tab afterward as long as that pattern is at the end of the string. Let us know which of these assumptions are not valid.

Sub findFour()
    Dim i As Integer
    Dim j As Integer
    Dim numberCounter As Integer
    Dim stringLength As Integer
    Dim currentString As String
    
    For i = 1 To 7
        numberCounter = 0
        currentString = Range("a" & i).Value
        stringLength = Len(currentString)
        If stringLength < 5 Then
            'don't do anything
        Else
            For j = 1 To stringLength - 1
                If IsNumeric(Mid(currentString, j, 1)) Then
                    numberCounter = numberCounter + 1
                    If numberCounter = 4 Then
                        If Mid(currentString, j + 1, 1) = " " Then
                            'found a space after 4 consecutive numbers
                            If j + 1 = stringLength Then
                                'the space is at the end of the string
                                Range("a" & i).Value = Range("a" & i).Value & vbTab
                            Else
                                'must save the first part of the string & the 2nd
                                'concatenate the first with a tab, then the 2nd to the result
                            End If
                        End If
                    End If
                Else
                    numberCounter = 0
                End If
            Next j
        End If
    Next i
End Sub