VBA to remove spaces from specific part number string

[solidENM]

Hello,
I am trying to create a macro that will remove spaces from a column, IF it starts with "3 464 xxx xxx". Our vendor has spaces in their part numbers, but our system does not use them. Externally, we have to keep the spaces, but for internal use I need to remove those spaces.

I have other parts on a bom sheet, and only want to remove the spaces from this specific vendors parts. All of their parts begin with 3 464 , then the last 2 sets of 3digits are unique to that part. example: 3 464 xxx xxx.

Thanks

 

[mumps]

Assuming your part numbers are in column A starting at row 2, try:

Sub removeSpaces()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim partNum As Range
    For Each partNum In Range("A2:A" & LastRow)
        If Left(partNum, 5) = "3 464" Then
            partNum = Trim(Replace(partNum, " ", ""))
        End If
    Next partNum
    Application.ScreenUpdating = True
End Sub

 

[solidENM]

worked like a charm! Thanks for the help, I was expecting this to take longer.

can you confirm if this logic is how your code works? I have been trying to understand more vba. It looks like it signifies the string as partNum, then using trim/replace to swap any space with "". is that correct, and is there any steps id be missing if i try to implement this in a different way in the future?

 

[mumps]

You are very welcome. The macro defines partNum as a range so it can search for it in column A and you are correct about replacing the space with a blank. How it will work in another situation in the future depends on how the data is organized and if the part number is in exactly the same format. If the format is different and/or the data is organized differently, then the macro will have to be modified to work with the new situation.

 

[Rick Rothstein]

Assuming your part numbers are in column A starting at row 2, try:

Sub removeSpaces()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim partNum As Range
    For Each partNum In Range("A2:A" & LastRow)
        If Left(partNum, 5) = "3 464" Then
            partNum = [B][COLOR="#FF0000"]Trim([/COLOR][/B]Replace(partNum, " ", "")[B][COLOR="#FF0000"])[/COLOR][/B]
        End If
    Next partNum
    Application.ScreenUpdating = True
End Sub

You don't actually need the Trim function call highlighted above... once you remove all the spaces, there will never be any left to trim afterwards.

For those who might be interested, there is a non-looping macro available for this question...

[table="width: 500"]
[tr]
	[td]Sub RemoveSpacesFrom3464Codes()
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("IF(LEFT(@,5)=""3 464"",SUBSTITUTE(@,"" "",""""),IF(@="""","""",@))", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[solidENM]

congrats on 32K posts Rick. Is the non-looping version faster or more stable? Im not sure what the difference is.

 

[Rick Rothstein]

congrats on 32K posts Rick.
.

Hey, thanks for pointing that out to me, I did not even notice that I had crossed the 32,000 post mark.

Is the non-looping version faster or more stable? Im not sure what the difference is.

I believe it will be slightly faster.

 

[Peter_SSs]

Is the non-looping version faster or more stable?

There is no inherent advantage/disadvantage in looping v non-looping. It depends partly on what is being looped and can also be influenced by a number of factors that will be specific to each case. However, in your case, with about 10,000 rows of which ..
- about 50% need the spaces removed, the looping code below is, by my testing, about 10% faster than the non-looping code suggested above.
- about 10% need the spaces removed it is about 45% faster than the non-looping code.

Sub Remove_Spaces()
  Dim a As Variant
  Dim i As Long

  Application.ScreenUpdating = False
    With Range("A2", Range("A" & Rows.Count).End(xlUp))
      a = .Value
      For i = 1 To UBound(a)
        If InStr(1, a(i, 1), "3 464") = 1 Then a(i, 1) = Replace(a(i, 1), " ", "")
      Next i
      .Value = a
    End With
  Application.ScreenUpdating = True
End Sub

Of course if you only have a few hundred or a few thousand rows, the time difference between any of the codes will be irrelevant & you should pick the one that you are most comfortable with, particularly if you might have to maintain it in the future.