VBA put space between string and numbers

[Bablu]

Hi All,

I have a quick request. I have some data in a cell B2 such as Feeder5846. What I would like to do is a put 2 spaces between the letters and numbers so that it is like Feeder 5846.

Currently, I have a formula to do this like =LEFT(B2,6)&" "&RIGHT(B2,LEN(B2)-6)

I would like to do this using VBA such as...

For each cell in selection

Cell.value = "VBA command goes here"

Next

This is not so much out neccesity - I want know how to do this. I searched for it on the web but did not find anything.

Any help would be great.

Thanks,

Bablu

 

[rdw72777]

1. What column is the initial value (Feeder5846) in?
2. What row does the data start on?
3. Are there any blank cells in the row?
4. Is the data always 10 characters and split with 6 test cells followed by 4 numeric cells?

 

[Bablu]

1. Initial value is in Column B1
2. That is Row 1.
3. There are no blank cells.
4. It is not always 10 characters. It is varying, but it always starts with letters first and then the numbers.

Thanks,

Bablu

 

[Scott Huish]

Sub test()
Dim c As Range
For Each c In Selection
    c = InsertSpace(c.Text)
Next
End Sub

Function InsertSpace(str As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "([a-z])(\d)"
        InsertSpace = .Replace(str, "$1 $2")
    End With
End Function

This uses Regular Expressions, see here for more info: http://www.regular-expressions.info/

 

[Scott Huish]

The pattern my code is looking for is a lowercase letter followed by a number, if you have capital letters followed by numbers as well, it will need a small adjustment.

 

[Bablu]

Looks like both methods do not work if the letters are capitalized, i.e, works with feeder5846 but did not work with FEEDER5846.

Another question is how to put 2 spaces.

Thanks so much

Bablu

 

[Bablu]

Sorry - looks like you already mentioned about the lowercase letters. I am not sure what adjustment I would have to make.

And if you could tell me how to put 2 spaces in both methods.

Thanks,

Bablu

 

[Scott Huish]

Sub test()
Dim c As Range
For Each c In Selection
    c = InsertSpace(c.Text)
Next
End Sub

Function InsertSpace(str As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "([A-Za-z])(\d)"
        InsertSpace = .Replace(str, "$1  $2")
    End With
End Function

 

[Bablu]

Works perfectly. I appreciate it

Bablu

 

[omnipotentmero]

I have a fairly similar situation, where I am trying to clean up some university course codes

Some are properly formatted :

ABC 123
ABC 123; ABC 456
​

But others need a space inserted:

ABC123
ABC123; ABC456
​

The example code provided above works just fine, if I am only looking at problem cells, but it crashes excel when I've tried to run it on the full column (where not all cells need to be fixed). I assume that I need to add some sort of "test if condition is met" line, but I am pretty new to VBA and regular expression. Can anyone help?

Thanks.