E excelent New Member Sep 20, 2015 #1 ok folks here's my challenge to you, write a formula shorter than mine Find the word (GOAL) after the 6' komma (,) always 4 character. 2009-01-15,35.80,36.50,34.30,35.30,13993900,GOAL,0,HEY,34
ok folks here's my challenge to you, write a formula shorter than mine Find the word (GOAL) after the 6' komma (,) always 4 character. 2009-01-15,35.80,36.50,34.30,35.30,13993900,GOAL,0,HEY,34
N NARAYANK991 Excel Ninja Sep 20, 2015 #2 Hi , Why don't you also post the length of the formula you have developed ? Narayan
E excelent New Member Sep 20, 2015 #3 The string : 2009-01-15,35.80,36.50,34.30,35.30,13993900,GOAL,0,HEY,34 is in cell B3 my formula length is 47
The string : 2009-01-15,35.80,36.50,34.30,35.30,13993900,GOAL,0,HEY,34 is in cell B3 my formula length is 47
Chihiro Excel Ninja Sep 20, 2015 #4 47? I'm guessing you are using variation on these. http://www.extendoffice.com/documents/excel/1052-excel-find-nth-occurrence.html http://excel.tips.net/T003324_Finding_the_Nth_Occurrence_of_a_Character.html Can't get shorter than 47 unless UDF is used =MID(B3,FIND("~",SUBSTITUTE(B3,",","~",6))+1,4)
47? I'm guessing you are using variation on these. http://www.extendoffice.com/documents/excel/1052-excel-find-nth-occurrence.html http://excel.tips.net/T003324_Finding_the_Nth_Occurrence_of_a_Character.html Can't get shorter than 47 unless UDF is used =MID(B3,FIND("~",SUBSTITUTE(B3,",","~",6))+1,4)
E excelent New Member Sep 20, 2015 #5 Hi Chihiro Yes the one you posted is almost excactly the same as mine =MID(B3,FIND("#",SUBSTITUTE(B3,",","#",6))+1,4) except for the #/~ well i didnt se that solusion before i wrote this one back in 2012 in a Dennish forum named eksperten.dk so i guess we cant get it shorter than this,- well 47 is not that bad thanks for your input.
Hi Chihiro Yes the one you posted is almost excactly the same as mine =MID(B3,FIND("#",SUBSTITUTE(B3,",","#",6))+1,4) except for the #/~ well i didnt se that solusion before i wrote this one back in 2012 in a Dennish forum named eksperten.dk so i guess we cant get it shorter than this,- well 47 is not that bad thanks for your input.
chirayu Well-Known Member Sep 21, 2015 #6 LEN 27 (Dunno if u meant this) =MID(A1,FIND("GOAL",A1,1),4)
E excelent New Member Sep 21, 2015 #7 Hi chirayu Unfortunately that one woult do, imagine that the Word after 6' komma was SOAL..!!! right, u never know what the Word would be, it could be a lot of different strings to handle. but thanks anyway
Hi chirayu Unfortunately that one woult do, imagine that the Word after 6' komma was SOAL..!!! right, u never know what the Word would be, it could be a lot of different strings to handle. but thanks anyway
H Haz Active Member Dec 7, 2015 #8 =MID(B3,FIND(",????,",B3)+1,4) It works when the word you're looking for is the first 4-letter word/number from the possible values.
=MID(B3,FIND(",????,",B3)+1,4) It works when the word you're looking for is the first 4-letter word/number from the possible values.
shaikhrulez Active Member Dec 30, 2015 #9 Why can't we go for this? =MID(A1,45,4) shorter and simpler. @Khalid NGO @excelent Last edited: Dec 30, 2015
Khalid NGO Excel Ninja Dec 30, 2015 #10 shaikhrulez said: Why can't we go for this? =MID(A1,45,4) shorter and simpler. @Khalid NGO @excelent Click to expand... Hi Waqar, That's okay for the specific entry. But suppose if the position of word "GOAL" is somewhere else? e.g. 2009-01-15,35.80,36.50,34.30,35.30,13993900,0,GOAL,HEY,34 Hi @Haz, I think we can not use wildcard in "FIND", I've assumed you were referring "SEARCH" Regards,
shaikhrulez said: Why can't we go for this? =MID(A1,45,4) shorter and simpler. @Khalid NGO @excelent Click to expand... Hi Waqar, That's okay for the specific entry. But suppose if the position of word "GOAL" is somewhere else? e.g. 2009-01-15,35.80,36.50,34.30,35.30,13993900,0,GOAL,HEY,34 Hi @Haz, I think we can not use wildcard in "FIND", I've assumed you were referring "SEARCH" Regards,