Stripping nubers from text in a cell

[Dan S]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Coderre]

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)

 

[T. Valko]

*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,255))

 

[Ron Coderre]

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

Ron

 

[T. Valko]

Needs to trim the space in the first example.

 

[Ron Coderre]

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 Rosenfeld]

=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

 

[Dan S]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Coderre]

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

Regards,

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