Separate at first number

J

J.W. Aldridge

Have a string of codes similar to below.

Any way to separate (LEFT, or RIGHT) all text to the right of the
first number in the cell?
(Some codes have more/less characters before and after zero, so using
right or left by itself may not work.)

apples12havocx
cherries84toughx
plums45easyxx
 
D

Dave Peterson

This array formula will give the position of the first digit in the string:

=MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

I'm not sure what should happen next, but if you put that formula in B1, you
could use:
=left(a1,b1-1)
to return: apples

=mid(a1,b1,len(a1))
to return: 12havocx
 
R

Ron Rosenfeld

Have a string of codes similar to below.

Any way to separate (LEFT, or RIGHT) all text to the right of the
first number in the cell?
(Some codes have more/less characters before and after zero, so using
right or left by itself may not work.)

apples12havocx
cherries84toughx
plums45easyxx

What do you mean by "first number"?

To return everything to the right of the first digit,

=MID(A1,1+MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0,1,2,3,4,5,6,7,8,9")),99)

If you want something else, you'll need to be more specific in your request,
and also in defining what kinds of "numbers" might be present.
--ron
 
R

RagDyer

Since you said right or left, this gives you the text that's to the left of
the first number:

=TRIM(LEFT(A1,MIN(FIND({"1","2","3","4","5","6","7","8","9","0"},A1&"1,2,3,4,5,6,7,8,9,0"))-1))
 
R

Rick Rothstein \(MVP - VB\)

What do you mean by "first number"?
To return everything to the right of the first digit,

=MID(A1,1+MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0,1,2,3,4,5,6,7,8,9")),99)

If the OP meant the full number shown (for example, the 12 in
"apples12havocx"), and IF that number is NEVER followed by other digits
appearing later on in the text, then this will return the text after that
number.

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1))),999)

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top