Formula or Macro? If, Find, Len?

[Dazzawm]

I have column E. Inside it is various length text but I need it to concentrate on the last 3 digits. I need it to replace it with the first 3 characters in column R.

e.g column E may have NIL_ALL in it so I need it to look and see if anything is in column R and if there is replace 'ALL' with the first 3 in R, which may be Hatch so it will become NIL_HAT in E. If there is nothing in R (in the same row of course) then do nothing. A macro would be better where it would replace what is in E, however I could put a helper column in with a formula. Thanks.

Just to confuse things if the word cabrio is found in R then I need the 3 letters to be CON not CAB, if this provides a problem I can do a find/replace after.

 

[adam087]

Sorry. I was being malcoordinated.

Sub test()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("R" & i).Value <> "" Then Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) & UCase(Left(Replace(Range("R" & i).Value, "Cabrio", "CON"), 3))
Next i
End Sub

Regards
Adam

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("R" & i).Value <> "" Then Range("E" & i).Value = Left(Range("E" & i).Value, Len(Range("E" & i).Value) - 3) _
    & UCase(Left(Replace(Range("R" & i).Value, "Cabrio", "CON"), 3))
Next i
End Sub

 

[Dazzawm]

Thank you both. Adam where in Southampton are you? I am in Southampton too!

 

[adam087]

Small world hey. I live by Dock Gate 4 (the actual dock gate 4, not the nightclub). I work at the moment in Chandler's Ford although I'm changing job next week to work between Romsey & Winchester.

You?

Regards
Adam

 

[Dazzawm]

Sorry I noticed one slight error. It needs to start in row 2 and not affect the headers in row 1.

I work in Portswood and live in Shirley.

 

[adam087]

Sorry I noticed one slight error. It needs to start in row 2 and not affect the headers in row 1.

I work in Portswood and live in Shirley.

Change the loop so i starts at 2.

Will drop you a PM next time we're headed out for some beer!

Regards
Adam

 

[Dazzawm]

Will drop you a PM next time we're headed out for some beer!

Regards
Adam

Thanks, yes please do that.

 

[Dazzawm]

Hi again Adam/Vog. Could the code work in the reverse. If the last 3 in E are the following can the words be put in R?

E R

HAT - Hatch
SAL - Saloon
COU - Coupe
EST - Estate
VAN - Van
MPV - MPV
CON - Cabrio

 

[adam087]

Yeah. I'd drop them into a lookup table somewhere then reference them at your leisure.
[R2] =VLOOKUP(RIGHT(E2, 3), MyLookupSheet!$A$2:$B$8, 2, False)

Regards
Adma

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Select Case Right(Range("E" & i).Value, 3)
        Case "HAT": Range("R" & i).Value = "Hatch"
        Case "SAL": Range("R" & i).Value = "Saloon"
        Case "COU": Range("R" & i).Value = "Coupe"
        Case "EST": Range("R" & i).Value = "Estate"
        Case "Van": Range("R" & i).Value = "Van"
        Case "MPV": Range("R" & i).Value = "MPV"
        Case "CON": Range("R" & i).Value = "Cabrio"
    End Select
Next i
End Sub