If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
help needed
eg. "mick jagger bush" "muhammad ali clay" "ayesha bin batool"
suppose that these r the names and i want formula that would be generic to all, such that it gets "jagger" "ali" and "bin" |
#2
|
|||
|
|||
help needed
One way:
=TRIM(MID(LEFT(A1, FIND("$", SUBSTITUTE(A1, "_", "$", 2))), FIND("_", A1), 32767)) where _ represents a space character to prevent unfortunate linewrap. In article , "zomex" wrote: eg. "mick jagger bush" "muhammad ali clay" "ayesha bin batool" suppose that these r the names and i want formula that would be generic to all, such that it gets "jagger" "ali" and "bin" |
#3
|
|||
|
|||
help needed
Hi Zomex
your teacher is obviously in a mean mood today for this one - one answer involves a combination of MID and SEARCH functions the way to approach it is - locate the first space using the SEARCH function - locate the second space using the SEARCH function (you'll have to nest the first formula in here again to get your starting point), then subtract the first formula again to calculate the number of chatacters between the two spaces. then nest these two formulas in the MID function. Using the above method will give you leading and trailing spaces, these can easily be edited out by modifying the formula (once you get that far). Give it a go and if you'ld like more help, just post back. Cheers JulieD julied at hctsReMoVeThIs dot net dot au "zomex" wrote: eg. "mick jagger bush" "muhammad ali clay" "ayesha bin batool" suppose that these r the names and i want formula that would be generic to all, such that it gets "jagger" "ali" and "bin" |
#4
|
|||
|
|||
help needed
thanks for the wonderful reply.... i couldnt get the middle name though the
formula that i was using sure did get me the last name and the3 formula is =MID(E4,FIND(" ",E4,FIND(" ",E4)+1),LEN(E4)) ...the other formula did work, but it shows the result like "0UsMan" and for even it shows "1UmEr"...how can i remove this "0" abd "1"... "JulieD" wrote: Hi Zomex your teacher is obviously in a mean mood today for this one - one answer involves a combination of MID and SEARCH functions the way to approach it is - locate the first space using the SEARCH function - locate the second space using the SEARCH function (you'll have to nest the first formula in here again to get your starting point), then subtract the first formula again to calculate the number of chatacters between the two spaces. then nest these two formulas in the MID function. Using the above method will give you leading and trailing spaces, these can easily be edited out by modifying the formula (once you get that far). Give it a go and if you'ld like more help, just post back. Cheers JulieD julied at hctsReMoVeThIs dot net dot au "zomex" wrote: eg. "mick jagger bush" "muhammad ali clay" "ayesha bin batool" suppose that these r the names and i want formula that would be generic to all, such that it gets "jagger" "ali" and "bin" |
#5
|
|||
|
|||
help needed
Maybe
=MID(E4,FIND(" ",E4,FIND(" ",E4)+1)+1,LEN(E4)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "zomex" wrote in message ... thanks for the wonderful reply.... i couldnt get the middle name though the formula that i was using sure did get me the last name and the3 formula is =MID(E4,FIND(" ",E4,FIND(" ",E4)+1),LEN(E4)) ...the other formula did work, but it shows the result like "0UsMan" and for even it shows "1UmEr"...how can i remove this "0" abd "1"... "JulieD" wrote: Hi Zomex your teacher is obviously in a mean mood today for this one - one answer involves a combination of MID and SEARCH functions the way to approach it is - locate the first space using the SEARCH function - locate the second space using the SEARCH function (you'll have to nest the first formula in here again to get your starting point), then subtract the first formula again to calculate the number of chatacters between the two spaces. then nest these two formulas in the MID function. Using the above method will give you leading and trailing spaces, these can easily be edited out by modifying the formula (once you get that far). Give it a go and if you'ld like more help, just post back. Cheers JulieD julied at hctsReMoVeThIs dot net dot au "zomex" wrote: eg. "mick jagger bush" "muhammad ali clay" "ayesha bin batool" suppose that these r the names and i want formula that would be generic to all, such that it gets "jagger" "ali" and "bin" |
#6
|
|||
|
|||
help needed
On Tue, 3 Jan 2006 21:19:02 -0800, "zomex"
wrote: eg. "mick jagger bush" "muhammad ali clay" "ayesha bin batool" suppose that these r the names and i want formula that would be generic to all, such that it gets "jagger" "ali" and "bin" =LEFT(REPLACE(A1,1,FIND(" ",A1),""),FIND(" ",REPLACE(A1,1,FIND(" ",A1),""))-1) Will find the first word after the first space. If there may be multiple spaces within the string, then replace all A1 with TRIM(A1) On the other hand, a little more complex, imagine that you may have name strings with 2, 3, or more names. You might want to extract the first name, last name, and all of the middle names separately. You also might want to be able to strip off Titles and such (e.g. Mr., Ms., Jr, III, Sr.) If this is a possibility, then regular expressions would be a more powerful tool to use. You can look at that by installing Longre's free morefunc.xll add-in from http://xcell05.free.fr First Name: =REGEX.MID(TRIM(A1),"\w+") Last Name: =REGEX.MID(TRIM(A1),"\w+$") Middle Names: =REGEX.MID(TRIM(A1),"(?=\s)(\w+\s)+") Note that the formula for Middle Names will return a blank if there are only two names (i.e. no middle name). --ron |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
CLOSEST TEMPLATE TO REQST MORE INFO NEEDED TO UPDT THE ACCT | MSANTOINETTE55 | General Discussion | 0 | November 8th, 2005 07:29 PM |
what information is needed to set up microsoft exchange server wi. | honkyd | Installation & Setup | 1 | May 15th, 2005 02:31 PM |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 08:39 AM |
Microsoft Office 2003, Addons, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] | athens.gr. | General Discussions | 1 | September 3rd, 2004 02:43 AM |
Expanding Data As Needed | MT | General Discussion | 2 | July 1st, 2004 12:52 AM |