Thread: Mid Function
View Single Post
  #7  
Old 09-19-2006, 06:09 PM
VBA Noob's Avatar
VBA Noob VBA Noob is offline
Wicked Forum Moderator
  
Join Date: 25 Apr 2006
Location: London, England
MS Office Version:Excel 2002
Posts: 11,242
VBA Noob is a jewel in the rough
Hi,

This is shorter by 288 characters

=IF(LEN(SUBSTITUTE(TRIM(A8),CHAR(32),CHAR(32)&CHAR(32)))-LEN(TRIM(A8))+1>2,
LEFT(A8,1)&" "&UPPER(MID(A8,FIND(" ",A8,1)+1,1))&" "&RIGHT(TRIM(A8),LEN(TRIM(A8))
-IF(ISERROR(FIND(" ",TRIM(A8),FIND(" ",TRIM(A8),1)+1)),FIND(" ",TRIM(A8),1),FIND(" ",TRIM(A8),FIND(" ",TRIM(A8),1)+1))),LEFT(A8,1)
&" "&RIGHT(TRIM(A8),LEN(TRIM(A8))
-IF(ISERROR(FIND(" ",TRIM(A8),FIND(" ",TRIM(A8),1)+1)),FIND(" ",TRIM(A8),1),FIND(" ",TRIM(A8),FIND(" ",TRIM(A8),1)+1))))

VBA Noob
Reply With Quote