Extract alpha and numeric values

[itty]

If one cell contain both alpha and numeric values, can we extract ou
these sepratley in excel. For eg. if cell no A1 contains the valu
"BS-Side Panel 342.5x560x18-Grey", can we take out 342.5x560x18 i
another cell?

Thanks in advance.....
Itt

 

[Frank Kabel]

Hi
a little bit difficult due to you text values 'x' in between. Could you
provide some more details about the logic of your data. e.g. fixed
lengths, etc.

 

[Leo Heuser]

itty

If the first and last character in the group (342.5x560x18)
is numeric, here's one way to do it:
The formula must be entered as *one* line.

=MID(A1,MIN(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:" &
LEN(A1))),1)+0),ROW(INDIRECT("1:" & LEN(A1))))),MAX(IF(
ISNUMBER(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)+0),
ROW(INDIRECT("1:" & LEN(A1)))))-MIN(IF(ISNUMBER(MID(A1,
ROW(INDIRECT("1:" & LEN(A1))),1)+0),ROW(INDIRECT("1:" & LEN(A1)))))+1)

The formula is an array formula and must be entered with
<Shift><Ctrl><Enter> , also if edited later.

 

[Frank Kabel]

Hi Leo
just as alternative
=MID(A1,MIN(IF(ISNUMBER(-MID(A1,seq,1)),seq)),LOOKUP(2,1/(ISNUMBER(-MID
(A1,seq,1))),seq)-MIN(IF(ISNUMBER(-MID(A1,seq,1)),seq))+1)
also an array formula

with seq as defined name with the formula
=ROW(INDIRECT("1:1024"))

 

[hgrove]

itty wrote...

If one cell contain both alpha and numeric values, can we
extract out these sepratley in excel. For eg. if cell no A1 contains
the value "BS-Side Panel 342.5x560x18-Grey", can we take out
342.5x560x18 in another cell?

If you mean to extract the substring between the leftmost and rightmos
decimal numerals inclusive, you could try the formula

=MID(LEFT(A1,LOOKUP(2,1/MID(A1,ROW(INDIRECT("1:32767")),1),
ROW(INDIRECT("1:32767")))),LOOKUP(2,1/MID(A1,32767-ROW(INDIRECT("1:32767")),1),
32767-ROW(INDIRECT("1:32767"))),32767)

If you need to use this formula in many cells, you could save spac
(but not recalc time) by defining names like MAXLEN referring to th
number constant 32767 and SEQ referring to the formul
=ROW(INDIRECT("1:"&MAXLEN)). Then you could reduce the formula abov
to

=MID(LEFT(A1,LOOKUP(2,1/MID(A1,SEQ,1),SEQ)),
LOOKUP(2,1/MID(A1,MAXLEN-SEQ,1),MAXLEN-SEQ),MAXLEN)

These needn't be entered as array formulas