Return text in a cell up to first number

[sharkbot]

I need to extract the first part of a cell that contains text. For example:

Kirk and Dunn 334 W.

I need to return just:

Kirk and Dunn

The same for when it looks like his:

Kirk and Dunn334

I need just:

Kirk and Dunn

Please help, I have been racking my brain for hours trying to figure this out. Thank YOU

Aaron

 

[Yogi Anand]

I need to extract the first part of a cell that contains text. For example:

Kirk and Dunn 334 W.

I need to return just:

Kirk and Dunn

The same for when it looks like his:

Kirk and Dunn334

I need just:

Kirk and Dunn

Please help, I have been racking my brain for hours trying to figure this out. Thank YOU

Aaron

Hi Aaron:

Welcome to MrExcel Board!

Let us have a look at ...

y040213h1a.xls
	A	B	C	D
1	KirkandDunn334W.	KirkandDunn
2	KirkandDunn334	KirkandDunn
Sheet3

This is according to your specs -- but I believe your specs need to be tightened a bit. Anyway, it should get us started.

 

[sharkbot]

Yogi you are a lifesaver!

Thanks so much. This is a great board. I can't believe how fast you replied. Thanks again, I am forever in debt to you.

 

[Yogi Anand]

Aaron: You Are Very Welcome!

You may want to tighten up the spec a bit -- for example handling a case where there are no numerics in a string, or any other such eventuality that you think you may encounter in your real data.

 

[Seti]

Also, if the data is "Kirk and Dunn 334", do you want/need the space after Dunn and before 334?