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

 

[Rick Rothstein]

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

If you type the name in yourself, the code works fine. The problem is the spaces in the OP's posted text are not regular spaces (ASCII 32), rather, they are non-breaking spaces (ASCII 160) which are usually acquired by copying from the web. If you change this line of code...

ray = VBA.Split(Name, " ")

to this...

ray = VBA.Split(Replace(Name, Chr(160), " "), " ")

then the code should work fine.

 

[muhammad susanto]

hi guys....

thanks for your support.....very nice!!

 

[footoo]

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

It works for me.
There needs to be at least 4 words - see original post

 

[Michael M]

Ok, thanks Rick .....AND footoo...