Name cosolidation

[bebo021999]

Hi every body, could you please help me

I have list of Full Name in column A and wanna get result in column B:

Column A / Column B

<TABLE style="WIDTH: 248pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=330><COLGROUP><COL style="WIDTH: 163pt; mso-width-source: userset; mso-width-alt: 7936" width=217><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 163pt; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl110 height=17 width=217 align=left>Nguyen Ngoc Tuan Anh</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 85pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl109 width=113 align=left>AnhNNT</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl110 height=17 align=left>
Doan Thu Nguyet
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl109 align=left>NguyetDT</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl110 height=17 align=left>Tran Viet</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl109 align=left>VietT

</TD></TR></TBODY></TABLE>

What formula can I use? (No VBA)

Thanks in advance

 

[Arul.rajesh]

considering the name is in "H1"

=LEFT(IFERROR(PROPER(RIGHT(H1,LEN(H1)-FIND("|",SUBSTITUTE(H1,CHAR(32),"|",LEN(H1)-LEN(SUBSTITUTE(H1,CHAR(32),"")))))),"")&UPPER(LEFT(H1,1))&IFERROR(UPPER(MID(H1,FIND("|",SUBSTITUTE(H1,CHAR(32),"|",1))+1,1)),"")&IFERROR(UPPER(MID(H1,FIND("|",SUBSTITUTE(H1,CHAR(32),"|",2))+1,1)),"")&IFERROR(UPPER(MID(H1,FIND("|",SUBSTITUTE(H1,CHAR(32),"|",3))+1,1)),"")&IFERROR(UPPER(MID(H1,FIND("|",SUBSTITUTE(H1,CHAR(32),"|",4))+1,1)),""),LEN(IFERROR(PROPER(RIGHT(H1,LEN(H1)-FIND("|",SUBSTITUTE(H1,CHAR(32),"|",LEN(H1)-LEN(SUBSTITUTE(H1,CHAR(32),"")))))),"")&UPPER(LEFT(H1,1))&IFERROR(UPPER(MID(H1,FIND("|",SUBSTITUTE(H1,CHAR(32),"|",1))+1,1)),"")&IFERROR(UPPER(MID(H1,FIND("|",SUBSTITUTE(H1,CHAR(32),"|",2))+1,1)),"")&IFERROR(UPPER(MID(H1,FIND("|",SUBSTITUTE(H1,CHAR(32),"|",3))+1,1)),"")&IFERROR(UPPER(MID(H1,FIND("|",SUBSTITUTE(H1,CHAR(32),"|",4))+1,1)),""))-1)


This function will give you what you want as long as the gap is a single space not char(160) and the name has at the most six parts.


VBA will definitely be bettter.

 

[Arul.rajesh]

<a href="http://www.imagehousing.com/image/856198"><img border="0" src="http://img1.imagehousing.com/24/1565e27d4641788785fca9b14b099bec.gif" alt="Free Image Hosting"></a>

It works on excel 2010 and will probably in 2007, but not in earlier versions. And Use the Trim function before use the formula.

 

[Arul.rajesh]

Is there an easier way without VBA? An array function maybe?

 

[bebo021999]

Thank you a lot. That is perfect!

 

[Arul.rajesh]

Thanks for the feedback.