View Full Version : Formula Help
YellowLabPro
11-28-2008, 10:16 AM
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
11-28-2008, 10:36 AM
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
11-28-2008, 11:49 AM
Hi Doug,
Code an option?
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
Dusty
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.