Home 
Search 
Today's Posts 
#1




Formula for Extracting Alphabetic Part of a Product Code
Hi, I've got a column filled with part #s having a format like ABC1439 where the number of letters varies between 24 and the number of digits varies between 34. Ie these are all possible numbers: AB145, BDA1457, KDOG145, etc. What I'd like is to be able to extract the alphabetic part of the part #s. I know I could probably work something out with nested If statements, IsText(), Right() and Left() but that would be really messy. Theres gotta be an easier way than that! Note that IsText() reports ACB12 as true, so I'd have to test IsText on the right side until I get false and then find the length of the string and subtract from that and take left() of the result... what a pain! I know I can do it in VB as well, but since I've already got a formula in place (it only takes the leftmost two letters) it would be easiest just to change the formula. Thanks for any tips or hints! Tom  ob3ron02  ob3ron02's Profile: http://www.excelforum.com/member.php...o&userid=15450 View this thread: http://www.excelforum.com/showthread...hreadid=273584 
#2




Hi
try the folloÂ´wing array formula (entered with CTRL+SHIFT+ENTER): =LEFT(A1,MIN(IF(ISNUMBER(MID(A1,seq,1)),seq)1) where seq is the defined name with the formula: =ROW(INDIRECT("1:1024")) "ob3ron02" wrote: Hi, I've got a column filled with part #s having a format like ABC1439 where the number of letters varies between 24 and the number of digits varies between 34. Ie these are all possible numbers: AB145, BDA1457, KDOG145, etc. What I'd like is to be able to extract the alphabetic part of the part #s. I know I could probably work something out with nested If statements, IsText(), Right() and Left() but that would be really messy. Theres gotta be an easier way than that! Note that IsText() reports ACB12 as true, so I'd have to test IsText on the right side until I get false and then find the length of the string and subtract from that and take left() of the result... what a pain! I know I can do it in VB as well, but since I've already got a formula in place (it only takes the leftmost two letters) it would be easiest just to change the formula. Thanks for any tips or hints! Tom  ob3ron02  ob3ron02's Profile: http://www.excelforum.com/member.php...o&userid=15450 View this thread: http://www.excelforum.com/showthread...hreadid=273584 
#3




"Frank Kabel" ha scritto nel messaggio news Hi try the follo´wing array formula (entered with CTRL+SHIFT+ENTER): =LEFT(A1,MIN(IF(ISNUMBER(MID(A1,seq,1)),seq)1) where seq is the defined name with the formula: =ROW(INDIRECT("1:1024")) hy Frank, I think you have lost a "" in your formula: =LEFT(A1,MIN(IF(ISNUMBER(MID(A1,seq,1)),seq)1) ivano 
#4




"ivano" schrieb im Newsbeitrag
... "Frank Kabel" ha scritto nel messaggio news Hi try the follo´wing array formula (entered with CTRL+SHIFT+ENTER): =LEFT(A1,MIN(IF(ISNUMBER(MID(A1,seq,1)),seq)1) where seq is the defined name with the formula: =ROW(INDIRECT("1:1024")) hy Frank, I think you have lost a "" in your formula: =LEFT(A1,MIN(IF(ISNUMBER(MID(A1,seq,1)),seq)1) Hi no this was intentioanlly :) No need for a second '' sign as I just test if the returned character is a number and I don't care about the sign of this number for this comparison. Frsnk 
#5




B1: =SUBSTITUTE(A1,REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5, 6,7,8,9},A1&"0123456789")1),""),"") which must be confirmed with control+shift+enter instead of just with enter. A1 houses a value like ABC1439 ob3ron02 Wrote: Hi, I've got a column filled with part #s having a format like ABC1439 where the number of letters varies between 24 and the number of digits varies between 34. Ie these are all possible numbers: AB145, BDA1457, KDOG145, etc. What I'd like is to be able to extract the alphabetic part of the part #s. I know I could probably work something out with nested If statements, IsText(), Right() and Left() but that would be really messy. Theres gotta be an easier way than that! Note that IsText() reports ACB12 as true, so I'd have to test IsText on the right side until I get false and then find the length of the string and subtract from that and take left() of the result... what a pain! I know I can do it in VB as well, but since I've already got a formula in place (it only takes the leftmost two letters) it would be easiest just to change the formula. Thanks for any tips or hints! Tom  Aladin Akyurek  Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=273584 
#6




"Frank Kabel" ha scritto nel messaggio ... "ivano" schrieb im Newsbeitrag ... "Frank Kabel" ha scritto nel messaggio news Hi try the follo´wing array formula (entered with CTRL+SHIFT+ENTER): =LEFT(A1,MIN(IF(ISNUMBER(MID(A1,seq,1)),seq)1) where seq is the defined name with the formula: =ROW(INDIRECT("1:1024")) hy Frank, I think you have lost a "" in your formula: =LEFT(A1,MIN(IF(ISNUMBER(MID(A1,seq,1)),seq)1) Hi no this was intentioanlly :) No need for a second '' sign as I just test if the returned character is a number and I don't care about the sign of this number for this comparison. Frsnk You are right, Frank... I thought that yuor formula don't play for "", insteat missing a ")" =LEFT(A1,MIN(IF(ISNUMBER(MID(A1,seq,1)),seq))1) hy..ivano 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
revert formula insertion to old method  Setting up and Configuration of Excel  
How do I copy a formula in excel where part remains absolute the .  Excel Discussion (Misc queries)  
Bold part of formula results  Excel Discussion (Misc queries)  
What instead of an array formula part 2  Excel Discussion (Misc queries)  
my computor crashed and I lost my product code to reinstall how c.  Excel Discussion (Misc queries) 