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

 

[Rick Rothstein]

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?

Yes, selecting the entire column, with your code as written, would be a problem as it will physically look at each cell. To skip blank cells, you could put this as the first line of code in my function...

If Len(str) = 0 Then Exit Sub

Doing that would help a little, but you would still lose time as Excel looked at each and every cell in the column (when you select the entire column). You could "short-circuit" this problem by changing your "test" sub to this...

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

Note what I did with your For Each statement... that restricts the loop to cells within your data range even if you select the entire column to process.

 

[omnipotentmero]

I'll test that out later today. Thanks so much for the assistance. I really appreciate you taking the time to help me out and explain this.

 

[muhammad musharraf]

Hi i have similer issue i need to turn these serial numbers
3476bf
1448cu
6649ju
8843a
1843d
8497c
123ada
456dab
658sam

into this with space
3476 bf
1448 cu
6649 ju
8843 a
1843 d
8497 c
123 ada
456 dab
658 sam

 

[Rick Rothstein]

Give this macro a try...

Sub InsertSpaceInSerialNumber()
  Dim R As Long, P As Long, vArr As Variant
  vArr = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
  For R = 1 To UBound(vArr)
    For P = 1 To Len(vArr(R, 1))
      If Mid(vArr(R, 1), P, 1) Like "[!0-9]" Then
        vArr(R, 1) = WorksheetFunction.Replace(vArr(R, 1), P, 0, " ")
        Exit For
      End If
    Next
  Next
  Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row) = vArr
End Sub

Change the three red "A" letters to the letter designation for the column containing your serial numbers and change the green "2" on that same code line to the row number where your first serial number is located.

 

[Scott Huish]

Try this:

Sub test()
Dim r As Range
For Each r In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    r = Val(r) & " " & Replace(r, Val(r), "")
Next
End Sub

 

[Rick Rothstein]

Try this:

Sub test()
Dim r As Range
For Each r In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    r = Val(r) & " " & Replace(r, Val(r), "")
Next
End Sub

I believe my approach will execute quicker than yours; however, putting that aside for the moment, your code will not handle a serial number that starts with one or more zeroes correctly (the Val function will "eat" them up).

 

[Scott Huish]

Good point.