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 :

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]name[/TH]
[TH]expected result[/TH]
[/TR]
[TR]
[TD]PT. DHARMA BERKAH SERINDIT[/TD]
[TD]PT. DHARMA BERKAH S[/TD]
[/TR]
[TR]
[TD]PT. HALMAHERA KAYU[/TD]
[TD]PT. HALMAHERA KAYU[/TD]
[/TR]
[TR]
[TD]KOPERASI PETANI SAWIT MERANGKAI SEJAHTERA[/TD]
[TD]KOPERASI PETANI SAWIT MS[/TD]
[/TR]
[TR]
[TD]PT. INDO PATRIA PERTIWI[/TD]
[TD]PT. INDO PATRIA P[/TD]
[/TR]
</tbody>[/TABLE]

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...