CODEWITHSUNDEEP
excelvba
Luis
Luis

Reputation: 209

Add a space if a letter is adjacent to a number

I´m trying to unify the format of a large .xlsx file I received.

One of the problems I found, is that there are entries which "unique code" is "00UTract 32", "132Unit 359", "5555UT22"... and then I´ve found we´ve "00 UTract 32", "Unit 359, 132", and "22UT, 5555".

As you may suspect, there are duplicates, and I confirmed that was the case.

So, how should I do to add a space each time I find a letter next to a number, so I can start cleaning the mess easily?

Thanks!!!

Upvotes: 1

Views: 70

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Select the cells you wish to check/correct and run this macro:

Sub DataFixer()
    Dim r As Range, DoIt As Boolean
    Dim temp As String, CH As String, v As String
    Dim i As Long, L As Long
    For Each r In Selection
        temp = ""
        DoIt = False
        v = r.Value
        L = Len(v)
        CH = Mid(v, 1, 1)
        temp = CH
        For i = 2 To L
            CH = Mid(v, i, 1)
            If IsNumeric(Right(temp, 1)) And CH Like "[a-zA-Z]" Then
                DoIt = True
                temp = temp & " "
            End If
            temp = temp & CH
        Next i
        If DoIt Then r.Value = temp
    Next r
End Sub

The macro checks each select cell for occurrences of:

{number}{letter}

and replaces them with:

{number} {letter}

Upvotes: 1

Alex Szabo
Alex Szabo

Reputation: 3276

I'd probably do this the other way around assuming that the only difference in IDs are the spaces. Simply remove all spaces from that column, and you will get the same values, without having to deal with checking each character in a string.

This can be done via CTRL+H and no need to introduce VB in it.

Upvotes: 0

Related Questions