Solved: Write a Small Procedure for me please?

[YellowLabPro]

Hi guys-
If anyone has a quick solution to this, I would be grateful. I am tied up in something else for a few hours and cannot solve for this. Sorry to ask/poach... like this

I need a find/replace that will go through column Y and find "08" anywhere in the string and replace it after the second space in the string. It will need a space after the "08" too.
This:
~P BURTON BULLET SNB 08 BLK/YLW 154
Would get changed to:

~P BURTON 08 BULLET SNB BLK/YLW 154


thanks,

Doug

[p45cal]

Try the following, select the cells you want it to operate on first; it works on any cells, not just column Y:
Sub blah()
For Each cll In Selection.Cells
x = InStr(cll.Value, " 08 ")
If x > 0 Then
cll.Value = Replace(cll.Value, " 08 ", " ")
cll.Value = Application.WorksheetFunction.Substitute(cll.Value, " 08 ", " ", 1)
cll.Value = Application.WorksheetFunction.Substitute(cll.Value, " ", " 08 ", 2)
End If
Next cll
End Sub

[Bob Phillips]

Public Sub ProcessData()
Dim iLastRow As Long
Dim iNum As Long
Dim iSpace As Long
Dim oData As Range
Dim oCell As Range
Dim sFirst As String

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "Y").End(xlUp).Row
Set oData = Range("Y1").Resize(iLastRow)
With oData
Set oCell = .Find("08 ", LookIn:=xlValues, lookat:=xlPart)
If Not oCell Is Nothing Then
sFirst = oCell.Address
Do
iNum = InStr(oCell.Value, "08 ")
iSpace = InStr(InStr(oCell.Value, " ") + 1, oCell.Value, " ")
oCell.Value = Left$(oCell.Value, iSpace) & "08 " & _
Mid$(oCell.Value, iSpace + 1, iNum - iSpace - 1) & _
Right$(oCell.Value, Len(oCell.Value) - iNum - 2)
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing And oCell.Address <> sFirst
End If
End With
End With

End Sub

[YellowLabPro]

Thanks p45!
That was really nice the way you did that.
The substitute function you used, you have prameters of 1 and 2.
So you are removing it altogether, then what happens?

[YellowLabPro]

Thanks Bob,
I have to get something out in the next hour, I will then step through your code.... I see basically what is happening, but will need to step through it to get to the details. Cheers....

[p45cal]

Slight cock-up, remove the line:
cll.Value = Replace(cll.Value, " 08 ", " ") leaving:
Sub blah()
For Each cll In Selection.Cells
If InStr(cll.Value, " 08 ") > 0 Then
cll.Value = Application.WorksheetFunction.Substitute(cll.Value, " 08 ", " ", 1)
cll.Value = Application.WorksheetFunction.Substitute(cll.Value, " ", " 08 ", 2)
End If
Next cll
End Sub
(I was just experimenting.)

The first substitute line substitutes the first occurrence of " 08 " with a single space.
The second one substitutes the second occurrence of " " with " 08 ".

[YellowLabPro]

Ok... wow that is really powerful! And so easy to use!

[p45cal]

Ok... wow that is really powerful! And so easy to use!..sounds like phrase from a TV ad for a vacuum cleaner! (Incidentally, one of the few items that when you say "it really sucks" means it's really good! - and you can guess why I put items in bold.)