Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 29th 04, 12:59 PM
ob3ron02
 
Posts: n/a
Default 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 2-4 and the number of digits
varies between 3-4. 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   Report Post  
Old October 29th 04, 01:18 PM
Frank Kabel
 
Posts: n/a
Default

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 2-4 and the number of digits
varies between 3-4. 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   Report Post  
Old October 29th 04, 03:49 PM
ivano
 
Posts: n/a
Default


"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   Report Post  
Old October 29th 04, 04:08 PM
Frank Kabel
 
Posts: n/a
Default

"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   Report Post  
Old October 29th 04, 05:35 PM
Aladin Akyurek
 
Posts: n/a
Default


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 2-4 and the number of digits
varies between 3-4. 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   Report Post  
Old October 30th 04, 12:35 PM
ivano
 
Posts: n/a
Default


"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 01:49 PM
How do I copy a formula in excel where part remains absolute the . SRF Excel Discussion (Misc queries) 1 January 7th 05 01:41 PM
Bold part of formula results Rita Palazzi Excel Discussion (Misc queries) 4 December 16th 04 07:51 PM
What instead of an array formula part 2 Reg Besseling Excel Discussion (Misc queries) 2 December 10th 04 07:35 AM
my computor crashed and I lost my product code to reinstall how c. jamie Excel Discussion (Misc queries) 2 December 7th 04 06:08 PM


All times are GMT +1. The time now is 02:03 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ę2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017