Formula Help

[YellowLabPro]

I could use some help creating a statemtent that loops through a range and says:
Loop through the range in col. I and delete the first word and first space in the string of that cell in Col I.

example:
Autobahn Dual Wht. 52

would become

Dual Wht. 2

Thanks,

Doug

[Bob Phillips]

You can't do it with a formula in one move Doug.

What you can do is in an adjacent column use

=IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,255),A2)

copy that down.

You could then copy > Paset values on that column and delete the original

[rbrhodes]

Hi Doug,

Code an option?

[VBA]
Option Explicit
Sub DelSpace()
Dim cel As Range
Dim Rng As Range
Dim SpcCtr As Long
Dim Str2 As String
Dim LastRow As Long
'Speed
Application.ScreenUpdating = False
'Get Last Row of data
LastRow = Range("I" & Rows.Count).End(xlUp).Row

'Presumes Col I data starts in row 2
Set Rng = Range("I2:I" & LastRow)

'Do all
For Each cel In Rng
'Clean cell of extra (leading & trailing) spaces
cel = Trim(cel)
'Get position of first space character
SpcCtr = InStr(cel, " ")
'Check if space was found
If SpcCtr > 1 Then
'Write cell value minus first word&space
cel = Right(cel, Len(cel) - SpcCtr)
End If
'Clean cell of extra (leading & trailing) spaces
cel = Trim(cel)
Next cel

'Reset
Application.ScreenUpdating = False

'Clean memory
Set cel = Nothing
Set Rng = Nothing
End Sub

[/VBA]

Dusty