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

 

[mirabeau]

Select the cells you want to reformat (you can include already-formatted ones) and try running the following code

Sub includespace()
Dim a As Variant, i As Long
For Each a In Selection.Cells
For i = 1 To Len(a) - 1
    If (Not IsNumeric(Mid(a, i, 1))) * (IsNumeric(Mid(a, i + 1, 1))) Then
        a.Value = Application.Trim(Left(a, i) & " " & Right(a, Len(a) - i))
    End If
Next i, a
End Sub

 

[Rick Rothstein]

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.

Apologies to HOTPEPPER for jumping in, but this modification to his posted function should work for you...

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

Note the Replace I added does NOT have a dot in front of it... that was done on purpose.

 

[Rick Rothstein]

Select the cells you want to reformat (you can include already-formatted ones) and try running the following code

Sub includespace()
Dim a As Variant, i As Long
For Each a In Selection.Cells
For i = 1 To Len(a) - 1
    If (Not IsNumeric(Mid(a, i, 1))) * (IsNumeric(Mid(a, i + 1, 1))) Then
        a.Value = Application.Trim(Left(a, i) & " " & Right(a, Len(a) - i))
    End If
Next i, a
End Sub

Here is another way to write a non-RegExp function for this...

Function InsertSpace(str As String) As String
    Dim X As Long
    InsertSpace = Replace(str, " ", "")
    For X = 1 To Len(InsertSpace) - 1
      If Mid(InsertSpace, X, 2) Like "[A-Za-z]#" Then InsertSpace = Left(InsertSpace, X) & " " & Mid(InsertSpace, X + 1)
    Next
End Function

 

[omnipotentmero]

Hey All, thanks for the replies. Testing your various solutions this morning a couple of things have come up. Primarily, that data that I need to split up is a little more complex than I anticipated.

A little background on what I am doing may be helpful here. I have a giant (~80,000 row) spread sheet with curriculum information for several thousand university degrees. Each row is an individual course. The column I am trying to standardize at the moment deals with course prerequisites.

The simple cases (one single course) are being properly dealt with by all of the code variations you all kindly provided.

Example:

PSC101
PSC 102
HST222b

become

PSC 101
PSC 102
HST 222b

However, there are some other more complicated cases that I need to deal with. Sometimes there are multiple courses that are required, and in other cases there may be a string of plain text that I need to keep untouched. I am still digging through this spreadsheet so I have not yet found every tricky case, but here are some of the more complex examples

HIS101; HIS102; HIS200
ACC200 or BUS205;MGMT 250
ACC200 or BUS205;MGMT 250 | BUS207; BUS208
MBio5000 or Permission from the director

And all sorts of other combinations in no standardized order. About the only semi-standardized thing is the way that or | ; are used to describe how the various requirements relate to each other. (A team of us are going through to manually edit that part, yay.)

Some course prefixes do start with numbers, so in ever case, where a letter is touching a number I want a space inserted, but not where a number touches a letter.

So 35BS155s should be parsed as 35BS 155s.
------------------

After all that, what is going on with the code you all provided? Well, the regular expression based macros, find the first instance of ([A-Za-z])(\d) and split that up properly, but then ignore any other other cases in the same cell. It also removes ALL other spaces from the string.

The other non-regex examples do seem to find the multiple courses in the same cell and split them properly, however they are all removing ALL other spaces.

Since there is descriptive text (things like "Program Director permission required") and delimiters like " or " in these cells, it is important that I keep all of the existing spaces left as they are.

Any ideas?

 

[Rick Rothstein]

Does this macro do what you want?

Function InsertSpace(str As String) As String
    Dim X As Long
    InsertSpace = str
    For X = 1 To Len(InsertSpace) - 1
      If Mid(InsertSpace, X, 2) Like "[A-Za-z]#" Then InsertSpace = Left(InsertSpace, X) & " " & Mid(InsertSpace, X + 1)
    Next
    InsertSpace = WorksheetFunction.Trim(InsertSpace)
End Function

 

[omnipotentmero]

@Rick Rothstein

Hey, I think that worked. It does take a fairly long time to execute (I tested it on about 100 cells and it look about 90 seconds), but all things considered, I'd much rather let this run while I go get a coffee than have to spend days manually find/replacing every single combination.

Thank you!

(it is a little odd though, when I run the macro, I can visually see all the cells change within a few seconds, but excel just sits there "thinking" for a while)

 

[Rick Rothstein]

@Rick Rothstein

Hey, I think that worked. It does take a fairly long time to execute (I tested it on about 100 cells and it look about 90 seconds), but all things considered, I'd much rather let this run while I go get a coffee than have to spend days manually find/replacing every single combination.

Thank you!

(it is a little odd though, when I run the macro, I can visually see all the cells change within a few seconds, but excel just sits there "thinking" for a while)

I made a mistake when I called my code a "macro"... it is a UDF (user defined function). As such, you should be using it like this directly on the worksheet...

=InsertSpace(A2)

copying it down as needed. When I do that, there is no detectable delay whatsoever in my worksheet. If this is what you have done and you get a delay like you mentioned, then can you send me the workbook (you can delete personal information for privacy as it has no bearing on this function) as I would like to help you track down the problem. My email address is rickDOTnewsATverizonDOTnet (replace the obvious with the obvious).

 

[omnipotentmero]

Oh, I was using it along with the bit of code from before.

Sub test()
Dim c As Range
For Each c In Selection
    c = InsertSpace(c.Text)
Next
MsgBox "macro has finished!"
End Sub

Function InsertSpace(str As String) As String
    Dim X As Long
    InsertSpace = str
    For X = 1 To Len(InsertSpace) - 1
      If Mid(InsertSpace, X, 2) Like "[A-Za-z]#" Then InsertSpace = Left(InsertSpace, X) & " " & Mid(InsertSpace, X + 1)
    Next
    InsertSpace = WorksheetFunction.Trim(InsertSpace)
End Function

(This is where I show how new I am to this) I didn't know you make custom functions.... but you are correct, running as it as a function works as intended. I assume then, there was something about the subroutine that was causing excel to move so slowly.

 

[Rick Rothstein]

I assume then, there was something about the subroutine that was causing excel to move so slowly.

Not that I can see. Using as a code function the way you did also works (although it physically changes the original values directly)... when I did that with 100 selected cells, your "test" sub executed in the blink of an eye (actually faster), so I'm not sure why you saw such a slow reaction when you ran it. Maybe you have other code that is getting kicked of with each iteration of loop? I'd have to see your workbook to know for sure what is going on.

 

[omnipotentmero]

I think some conditional formatting rules were slowing it down, but mostly I think it was the fact that I was selecting the entire column, rather than just selecting the cells I wanted to fix.

Mostly just out of curiosity now, but is there a way to do a quick test & have excel just skip a cell if it is blank?