|
LEFT, MID functions?
Hi
Say I have the following in cell G3: 15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17) I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem is, the name won't always be 8 chars long, so is there any way that I can set it so that it counts all chars of the name until it comes to the space and then inserts that? I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm assuming that once B3 is sorted, it will work around that? And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for this one, but again, the chars of this one will differ (eg Claire Dunn instead of Lindsey Martin), any ideas on this one? Any help would be greatly appreciated Cheers Linds |
Hi
B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3,"")))-1) C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1) D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,"")) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Lindsey M" wrote in message ... Hi Say I have the following in cell G3: 15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17) I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem is, the name won't always be 8 chars long, so is there any way that I can set it so that it counts all chars of the name until it comes to the space and then inserts that? I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm assuming that once B3 is sorted, it will work around that? And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for this one, but again, the chars of this one will differ (eg Claire Dunn instead of Lindsey Martin), any ideas on this one? Any help would be greatly appreciated Cheers Linds |
Hi Avri
Thanks for your quick response, however, the first one (B3) just returns a blank cell and when I enter the formula in C3 it comes up with an error Sorry to be a pain, but any ideas? Linds "Arvi Laanemets" wrote: Hi B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3,"")))-1) C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1) D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,"")) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Lindsey M" wrote in message ... Hi Say I have the following in cell G3: 15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17) I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem is, the name won't always be 8 chars long, so is there any way that I can set it so that it counts all chars of the name until it comes to the space and then inserts that? I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm assuming that once B3 is sorted, it will work around that? And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for this one, but again, the chars of this one will differ (eg Claire Dunn instead of Lindsey Martin), any ideas on this one? Any help would be greatly appreciated Cheers Linds |
Hi
Is the date in A3 remained as text, or did you convert it to date? What does the formula =TRIM(SUBSTITUTE($G3,A3,"")) return. It must return "Stevens Feb 22 2005 Lindsey Martin" - when not, then try with =TRIM(SUBSTITUTE($G3,TEXT(A3,"dd.mm.yy hh:mm:ss"),"")) or =TRIM(SUBSTITUTE($G3,TEXT(A3*1,"dd.mm.yy hh:mm:ss"),"")) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Lindsey M" wrote in message ... Hi Avri Thanks for your quick response, however, the first one (B3) just returns a blank cell and when I enter the formula in C3 it comes up with an error Sorry to be a pain, but any ideas? Linds "Arvi Laanemets" wrote: Hi B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3,"")))-1) C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1) D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,"")) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Lindsey M" wrote in message ... Hi Say I have the following in cell G3: 15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17) I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem is, the name won't always be 8 chars long, so is there any way that I can set it so that it counts all chars of the name until it comes to the space and then inserts that? I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm assuming that once B3 is sorted, it will work around that? And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for this one, but again, the chars of this one will differ (eg Claire Dunn instead of Lindsey Martin), any ideas on this one? Any help would be greatly appreciated Cheers Linds |
Hi Arvi,
The first statement does bring up Stevens Feb 22 2005 Lindsey Martin, so it must be the rest thats not working. Cheers Lindsey "Arvi Laanemets" wrote: Hi Is the date in A3 remained as text, or did you convert it to date? What does the formula =TRIM(SUBSTITUTE($G3,A3,"")) return. It must return "Stevens Feb 22 2005 Lindsey Martin" - when not, then try with =TRIM(SUBSTITUTE($G3,TEXT(A3,"dd.mm.yy hh:mm:ss"),"")) or =TRIM(SUBSTITUTE($G3,TEXT(A3*1,"dd.mm.yy hh:mm:ss"),"")) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Lindsey M" wrote in message ... Hi Avri Thanks for your quick response, however, the first one (B3) just returns a blank cell and when I enter the formula in C3 it comes up with an error Sorry to be a pain, but any ideas? Linds "Arvi Laanemets" wrote: Hi B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3,"")))-1) C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1) D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,"")) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Lindsey M" wrote in message ... Hi Say I have the following in cell G3: 15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17) I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem is, the name won't always be 8 chars long, so is there any way that I can set it so that it counts all chars of the name until it comes to the space and then inserts that? I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm assuming that once B3 is sorted, it will work around that? And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for this one, but again, the chars of this one will differ (eg Claire Dunn instead of Lindsey Martin), any ideas on this one? Any help would be greatly appreciated Cheers Linds |
Hi
So continue with testing stepwise =FIND(" ",TRIM(SUBSTITUTE($G3,A3,""))) must return 8. When not, then probably you entered "" as first parameter instead " ". When this returns right number, and the whole formula in B3 doesn't return "Stevens", then you have misspelled something in MID function. Arvi Laanemets "Lindsey M" wrote in message ... Hi Arvi, The first statement does bring up Stevens Feb 22 2005 Lindsey Martin, so it must be the rest thats not working. Cheers Lindsey "Arvi Laanemets" wrote: Hi Is the date in A3 remained as text, or did you convert it to date? What does the formula =TRIM(SUBSTITUTE($G3,A3,"")) return. It must return "Stevens Feb 22 2005 Lindsey Martin" - when not, then try with =TRIM(SUBSTITUTE($G3,TEXT(A3,"dd.mm.yy hh:mm:ss"),"")) or =TRIM(SUBSTITUTE($G3,TEXT(A3*1,"dd.mm.yy hh:mm:ss"),"")) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Lindsey M" wrote in message ... Hi Avri Thanks for your quick response, however, the first one (B3) just returns a blank cell and when I enter the formula in C3 it comes up with an error Sorry to be a pain, but any ideas? Linds "Arvi Laanemets" wrote: Hi B3=MID(TRIM(SUBSTITUTE($G3,A3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3,"")))-1) C3=MID(TRIM(SUBSTITUTE($G3,A3 &" " & B3,"")),1,FIND(" ",TRIM(SUBSTITUTE($G3,A3 &" " & B3;"")),10)-1) D3=TRIM(SUBSTITUTE(G3,A3 & " " & B3 & " " &C3,"")) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Lindsey M" wrote in message ... Hi Say I have the following in cell G3: 15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17) I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem is, the name won't always be 8 chars long, so is there any way that I can set it so that it counts all chars of the name until it comes to the space and then inserts that? I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm assuming that once B3 is sorted, it will work around that? And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for this one, but again, the chars of this one will differ (eg Claire Dunn instead of Lindsey Martin), any ideas on this one? Any help would be greatly appreciated Cheers Linds |
On Wed, 16 Mar 2005 04:33:01 -0800, Lindsey M
wrote: Hi Say I have the following in cell G3: 15.02.05 11:09:52 Stevens Feb 22 2005 Lindsey Martin I want A3 = 15.02.05 11:09:52 (so i use =LEFT(G3,17) I want B3 = Stevens (so I've used =MID(G3, 19, 8) and this works, problem is, the name won't always be 8 chars long, so is there any way that I can set it so that it counts all chars of the name until it comes to the space and then inserts that? I want C3 = Feb 22 2005, i haven't worked this one out yet because I'm assuming that once B3 is sorted, it will work around that? And finally, D3 = Lindsey Martin. I know I can use the RIGHT function for this one, but again, the chars of this one will differ (eg Claire Dunn instead of Lindsey Martin), any ideas on this one? Any help would be greatly appreciated Cheers Linds A3: =LEFT(TRIM(G3),FIND(CHAR(1), SUBSTITUTE(TRIM(G3)," ",CHAR(1),2))-1) B3: =MID(TRIM(G3),FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ", CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ", CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ",CHAR(1),2))-1) C3: =MID(TRIM(G3),FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ", CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ", CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ",CHAR(1),3))-1) D3: =MID(TRIM(G3),FIND(CHAR(1),SUBSTITUTE(TRIM(G3)," ", CHAR(1),6))+1,255) --ron |
| All times are GMT +1. The time now is 07:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com