Stripping nubers from text in a cell
 

I have a bunch of cell with varying lench that have numbers at the beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan

Stripping nubers from text in a cell
 

Assuming your text is not longer than 999 characters and that your digits
are all next to each other (no leading or intervening spaces or
characters)...

=MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999)

Rick


"Dan S" wrote in message
...
I have a bunch of cell with varying lench that have numbers at the
beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan

Stripping nubers from text in a cell
 

With

A1: (containing text that may begin with numbers.....eg 1258 ahjKJ yuUIOI)

This formula (in sections to compensate for text wrap)
returns the text to the right of those beginning numbers:
B1: =MID(A1,MATCH(FALSE,INDEX(ISNUMBER(--LEFT(A1,ROW(INDEX($A:$A,1):
INDEX($A:$A,LEN(A1))))),0),0),LEN(A1))

In the above example, B1 returns: ahjKJ yuUIOI

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Dan S" wrote in message
...
I have a bunch of cell with varying lench that have numbers at the
beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan

Stripping nubers from text in a cell
 

*Maybe* this:

Assuming the number is *always* at the beginning of the string and that
there are no other numbers in the string.

=TRIM(MID(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))+1,25 5))


--
Biff
Microsoft Excel MVP


"Dan S" wrote in message
...
I have a bunch of cell with varying lench that have numbers at the
beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan

Stripping nubers from text in a cell
 

Certainly better than what I threw together.
(I don't know WHAT I was thinking.)

Ron



"Rick Rothstein (MVP - VB)" wrote in
message ...
Assuming your text is not longer than 999 characters and that your digits
are all next to each other (no leading or intervening spaces or
characters)...

=MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999)

Rick


"Dan S" wrote in message
...
I have a bunch of cell with varying lench that have numbers at the
beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan

Stripping nubers from text in a cell
 

Needs to trim the space in the first example.

--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
Certainly better than what I threw together.
(I don't know WHAT I was thinking.)

Ron



"Rick Rothstein (MVP - VB)" wrote in
message ...
Assuming your text is not longer than 999 characters and that your digits
are all next to each other (no leading or intervening spaces or
characters)...

=MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999)

Rick


"Dan S" wrote in message
...
I have a bunch of cell with varying lench that have numbers at the
beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan

Stripping nubers from text in a cell
 

Maybe this (If no other numbers will be embedded within the text):

=MID(A1,COUNT(INDEX(--MID(A1,ROW($1:$255),1),0))+1,LEN(A1))

or...if you need to remove leading/trailing spaces:
=TRIM(MID(A1,COUNT(INDEX(--MID(A1,ROW($1:$255),1),0))+1,LEN(A1)))

But, if there may be numbers in the text...the hideous formula I posted
earlier will do what you asked for.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Ron Coderre" wrote in message
...
With

A1: (containing text that may begin with numbers.....eg 1258 ahjKJ yuUIOI)

This formula (in sections to compensate for text wrap)
returns the text to the right of those beginning numbers:
B1: =MID(A1,MATCH(FALSE,INDEX(ISNUMBER(--LEFT(A1,ROW(INDEX($A:$A,1):
INDEX($A:$A,LEN(A1))))),0),0),LEN(A1))

In the above example, B1 returns: ahjKJ yuUIOI

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Dan S" wrote in message
...
I have a bunch of cell with varying lench that have numbers at the
beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan

Stripping nubers from text in a cell
 

On Thu, 6 Mar 2008 18:01:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

=MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999)

Slightly shorter <vbg

=MID(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW($1:$999),1)))+1,999)
--ron

Stripping nubers from text in a cell
 

I think that might do the trick...I wish there was something a little
simplier (built in the Excel) but I appreciate the help!

Dan


"Ron Coderre" wrote:

With

A1: (containing text that may begin with numbers.....eg 1258 ahjKJ yuUIOI)

This formula (in sections to compensate for text wrap)
returns the text to the right of those beginning numbers:
B1: =MID(A1,MATCH(FALSE,INDEX(ISNUMBER(--LEFT(A1,ROW(INDEX($A:$A,1):
INDEX($A:$A,LEN(A1))))),0),0),LEN(A1))

In the above example, B1 returns: ahjKJ yuUIOI

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Dan S" wrote in message
...
I have a bunch of cell with varying lench that have numbers at the
beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan

Stripping nubers from text in a cell
 

Certainly better than what I threw together.
(I don't know WHAT I was thinking.)

LOL... trust me, I know the feeling. As a matter-of-fact, I just did the
same type of thing down a few threads in identifying the first Monday in a
month. Fortunately for the OP, Ron Rosenfeld posted a saner formula to use.

Rick

Stripping nubers from text in a cell
 

I wasn't sure what to do about that. The OP said he wanted to extract the
text (non-numbers) and that leading space is text, so I left it in.

Rick


"T. Valko" wrote in message
...
Needs to trim the space in the first example.

--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
Certainly better than what I threw together.
(I don't know WHAT I was thinking.)

Ron



"Rick Rothstein (MVP - VB)" wrote
in message ...
Assuming your text is not longer than 999 characters and that your
digits are all next to each other (no leading or intervening spaces or
characters)...

=MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999)

Rick


"Dan S" wrote in message
...
I have a bunch of cell with varying lench that have numbers at the
beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan

Stripping nubers from text in a cell
 

Well, I'm just glad I could help.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Dan S" wrote in message
...
I think that might do the trick...I wish there was something a little
simplier (built in the Excel) but I appreciate the help!

Dan


"Ron Coderre" wrote:

With

A1: (containing text that may begin with numbers.....eg 1258 ahjKJ
yuUIOI)

This formula (in sections to compensate for text wrap)
returns the text to the right of those beginning numbers:
B1: =MID(A1,MATCH(FALSE,INDEX(ISNUMBER(--LEFT(A1,ROW(INDEX($A:$A,1):
INDEX($A:$A,LEN(A1))))),0),0),LEN(A1))

In the above example, B1 returns: ahjKJ yuUIOI

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Dan S" wrote in message
...
I have a bunch of cell with varying lench that have numbers at the
beginning
(also of varying length. Ex:

1258 ahjKJ yuUIOI
45687045HJKoiuwER

I just want to extract the text from these cell. How do I do that?

Thanks,
Dan