Extract To Be Nick Name With Initial in The last Word

[muhammad susanto]

hi all...

how to extract this text to be the nick name like this :

name	expected result
PT. DHARMA BERKAH SERINDIT	PT. DHARMA BERKAH S
PT. HALMAHERA KAYU	PT. HALMAHERA KAYU
KOPERASI PETANI SAWIT MERANGKAI SEJAHTERA	KOPERASI PETANI SAWIT MS
PT. INDO PATRIA PERTIWI	PT. INDO PATRIA P

<tbody>
</tbody>

any help..much appreciated...

m.sa

 

[footoo]

If VBA is OK :

Function NickName(Name As String)
Dim ray() As String, c%, i%, intl$
ray = VBA.Split(Name, " ")
c = UBound(ray)
If c < 3 Then
    NickName = Name
Else
    For i = 3 To c
        intl = intl & Left(ray(i), 1)
    Next
    NickName = Name & " " & intl
End If
End Function

Enter as =NickName(A2)

 

[muhammad susanto]

hi..thanks...
how it's work?..i try not work.

 

[footoo]

What did you do to try it?

 

[muhammad susanto]

copy paste your code in View Code, and use =NickName (A2)..
show =#name?..

your code is UDF? or other.

 

[footoo]

You need to put the Function in a normal module, not a sheet module.
Delete what you pasted before then :
Copy the Function
Press Alt+F11
Click Insert/Module
Paste the function to the window on the right
Assuming your names start in A2, enter in B2 and drag down =NickName(A2)

 

[muhammad susanto]

thanks...but still not work...=#name?

 

[footoo]

Is your file .xlsm ? If not it needs to be.

Other possible reason : you have a module named NickName.

 

[Michael M]

@footoo
Tried it myself, it simply returns cell A2 as is !!.....no change

 

[jtakw]

Hi,

This formula solution will work for names containing One to Five words, any more than 5 will need modification.

Book1
	A	B
1	name
2	PT. DHARMA BERKAH SERINDIT	PT. DHARMA BERKAH S
3	PT. HALMAHERA KAYU	PT. HALMAHERA KAYU
4	KOPERASI PETANI SAWIT MERANGKAI SEJAHTERA	KOPERASI PETANI SAWIT MS
5	PT. INDO PATRIA PERTIWI	PT. INDO PATRIA P
6	KOPERASI	KOPERASI
7	KOPERASI PETANI	KOPERASI PETANI
Sheet159

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND("^",SUBSTITUTE(A2&REPT(" ",3)," ","^",3))+1)&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",400),4),400,100)),1)