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]

Perhaps this formula:
=IF(R2="", E2, LEFT(E2, LEN(E2)-3)&UPPER(LEFT(SUBSTITUTE(R2, "Cabrio", "CON"), 3)))

Regards
Adam

 

[VoG]

Try this

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(Range("R" & i).Value, 3))
Next i
End Sub

 

[adam087]

Or this code?

Option Explicit
 
Public Sub CheckEntries()
 
    Dim rng, r As Range, s As String
 
    Set rng = ActiveSheet.Range("E2:E5") 'change as appropriate
 
    For Each r In rng
 
        If r.Value <> "" And r.Offset(0, 13).Value <> "" Then
            s = Left(r.Value, Len(r.Value) - 3) & UCase(Left(Replace(r.Offset(0, 13).Value, "Cabrio", "CON"), 3))
            r.Value = s
        Else
            'do nothing
        End If
 
    Next r
 
End Sub

Regards
Adam

 

[Dazzawm]

Thank you both. I assume with your 2 codes I will need to change CON/CAB manually?

 

[VoG]

Thank you both. I assume with your 2 codes I will need to change CON/CAB manually?

You will with mine.

 

[adam087]

Mine should catch it.

Regards
Adam

 

[Dazzawm]

Mine should catch it.

Regards
Adam

It does thanks. Could you adapt yours a little please Adam. I need it a bit like VoGs where it counts the range automatically so I can use it on all different size files, rather than changing the range manually each time. Thanks.

 

[adam087]

Use VOG's; it's neater. Just drop my formula in to his (not tested):

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 <> "" '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

 

[Dazzawm]

That doesnt work. When I put it in the module some of the writing has gone red.