CODEWITHSUNDEEP
excel
DMCApps
DMCApps

Reputation: 2168

Excel - Add space between number and word

I am looking for a way to put a space in between a text value and numerical value, but there is a hitch. Sometimes the text might start with a number as well and I don't want this to contain the space. For Example

Col A                 Col B
Name1:3-2             Name 1:3-2
Name6:5,4             Name 6:5,4
1 Val55:12-4          1 Val 55:12-4
2 Val 22:43           2 Val 22:43
Name10                Name 10

Where Col A is the value and Col B contains the formula to add the space to the value is Col A.

Few things to Note here:

  1. The space is always added to only the first number after the text ends.

  2. If the value starts with a number that one should be ignored and again the first number after the text should have the space added to it

  3. If there is already a space, another should not be added.

  4. The length of the text before the numbers varies

  5. There isn't always a : between values after the first number

The data set I am working with is about 1,000 entries so speed is not necessary just need something that works for all cases as I don't want to go through that many entries and add a space.

EDIT Final Solution Thanks to Gary's Student below:

' Function Assumes that there are atleast 2 characters in front of the expected space
' Function Assumes that there are no numbers within the text that will cause an early splitting of characters
Public Function SpacedOut(sIn As String) As String
    Dim L As Long, i As Long, Done As Boolean
    Dim sOut As String
    L = Len(sIn)
    Done = False
    sOut = Left(sIn, 1)
    ' Skips the first possible number if it is there if not, we are safe to start at 2
    ' Since there will always be more than one character prior to the expected first number
    For i = 2 To L
        ' Check for a number without a space before it
        If Mid(sIn, i - 1, 1) <> " " And Mid(sIn, i, 1) Like "[0-9]" And Not Done Then
            Done = True
            sOut = sOut & " " & Mid(sIn, i, 1)
        ' Check for a space with a number after it and continue on if this is found
        ElseIf Mid(sIn, i - 1, 1) = " " And Mid(sIn, i, 1) Like "[0-9]" And Not Done Then
            Done = True
            sOut = sOut & Mid(sIn, i, 1)
        ' Append next character
        Else
            sOut = sOut & Mid(sIn, i, 1)
        End If
    Next i
    SpacedOut = sOut
End Function

Thanks, DMan

Upvotes: 0

Views: 3143

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Try this small UDF :

Public Function SpacedOut(sIn As String) As String
    Dim L As Long, i As Long, Done As Boolean
    Dim sOut As String
    L = Len(sIn)
    Done = False
    sOut = Left(sIn, 1)
    For i = 2 To L
        If Mid(sIn, i - 1, 1) Like "[a-zA-Z]" And Mid(sIn, i, 1) Like "[0-9]" And Not Done Then
            Done = True
            sOut = sOut & " " & Mid(sIn, i, 1)
        Else
            sOut = sOut & Mid(sIn, i, 1)
        End If
    Next i
    SpacedOut = sOut
End Function

EDIT#1:

Here is a snap of my VBE screen with the module hi-lighted:

SNAP

Upvotes: 3

Related Questions