Quote:
|
Originally Posted by sujit_shukla
Hi, I want to know more about Mid function
Suppose there is a praticular name "David Jonathan Anthony" So I want only the two initials of first name and complete last name to be extracted. So it should read as "D A Anthony" and names could always change, Cud be "Sujit Shukla", "Sunil Satyadhar Shukla". Any suggestion.
|
Hi sujit_shukla,
The following formula doesn't use the MID function, but suprisingly it does do what you want. It the longest formula I've ever created - is it a record?
Put your data in A8, then this in B8
=IF(IF(IF(ISERROR(SEARCH(" ",RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1)),0,SEARCH(" ",RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1))=0,"",LEFT(RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1))="",LEFT(A8,1)&" "&RIGHT(RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),LEN(A8)-SEARCH(" ",A8,1)-IF(ISERROR(SEARCH(" ",RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1)),0,SEARCH(" ",RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1))),LEFT(A8,1)&" "&IF(IF(ISERROR(SEARCH(" ",RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1)),0,SEARCH(" ",RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1))=0,"",LEFT(RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1))&" "&RIGHT(RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),LEN(A8)-SEARCH(" ",A8,1)-IF(ISERROR(SEARCH(" ",RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1)),0,SEARCH(" ",RIGHT(A8,LEN(A8)-SEARCH(" ",A8,1)),1))))
Copy and paste, don't try to type it!
There must be a way to shorten it, if anybody can let me know please
oldchippy 
|