remove after three & four letters , keep the first three & four letters with shift capital

According to your attachment a VBA demonstration for starters :

PHP Code: 

Sub Demo1()
        Dim V, R&, L&
    With [A1].CurrentRegion.Columns(2)
            V = .Value
        For R = 2 To .Rows.Count
            L = InStrRev(V(R, 1), " ")
            V(R, 1) = Left(V(R, 1), L) & UCase(Mid(V(R, 1), L + 1, 3))
        Next
           .Value = V
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

thanks , but "INDONESIA" should be "INDO" as I siad . your code make it IND .

Originally Posted by abdo meghari

thanks , but "INDONESIA" should be "INDO" as I siad . your code make it IND .

Is 'INDONESIA' the only one to need one more character ?!

There needs to be a logic for which should contract to 3 and which to 4, or you'll need a lookup list.

JAPAN=JAP,THAILAND=THI,INDONESIA=INDO

You have misspelt THAILAND, by the way.

thanks for inform me

Is 'INDONESIA' the only one to need one more character ?!

yes this is the only

Hoping it is obviously 'THA' for Thailand in order to avoid a gas factory procedure …

So THAILAND will shorten to THA or THI???

again sorry! you're right, but it doesn't make difference because just delete after first three letters .it's not important who's the third letter should delete after it , A or I

My VBA demonstration revamped :

PHP Code: 

Sub Demo1r()
        Dim V, R&, S
    With [A1].CurrentRegion.Columns(2)
            V = Application.Trim(.Cells)
        For R = 2 To .Rows.Count
                S = Split(V(R, 1))
            If UBound(S) = 2 Then
                S(2) = UCase(S(2))
                S(2) = Left(S(2), 3 - (S(2) Like "INDO*"))
                V(R, 1) = Join(S)
            End If
        Next
           .Value = V
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Hoping it is obviously 'THA' for Thailand in order to avoid a gas factory procedure …

based on your code I think to don't affect for code working if the letter is wrong

@Marc L edited last post

As it well works on my side with your initial post attachment where I can't find what you describe …

sorry ! the item 42 in row43 235/85r16 D697 INDO should be 235/85R16 D697 INDO .

As finally it is not only for the third word so my revamped demonstration revised :

PHP Code: 

Sub Demo1r2d2()
        Dim V, R&, S
    With [A1].CurrentRegion.Columns(2)
        V = Evaluate("IF({1},UPPER(TRIM(" & .Address & ")))")
    For R = 2 To .Rows.Count
        S = Split(V(R, 1))
        If UBound(S) = 2 Then S(2) = Left(S(2), 3 - (S(2) Like "INDO*")): V(R, 1) = Join(S)
    Next
       .Value = V
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

and if any items contain letter is small then should convert to capital letters

I mentioned it , if it's not clear this is my bad ! sorry !
thanks very much for your assistance