Extracting text from a cell based on conditions

[aavvijit]

I am trying to write a piece of VB or a formula that will enable me to extract a text from a alphanumeric field. For example,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
A1 : WHYTEHALL CLASSIC 375ML CIVIL
A2 : SAV 750ML CIVIL
<o> </o>
Now I want to have my result as to extract all text characters until the beginning of numbers in a cell. (Example : “WHYTEHALL CLASSIC” for cell A1 and “SAV” for cell A2)
<o> </o>
To add to the problem is the fact that the position of the 1<SUP>st</SUP> number in the cell is not always the same - i.e. some cells contain number in 8 position and some in 10 position)<o></o>
<o> </o>
I've tried various methods but to no avail,
Hope someone can help<o></o>
<o> </o>
Thanks in advance.
Abhijit<o></o>
<o> </o>

 

[Colin Legg]

Welcome to the forum, Abhijit<o></o>.

Try this formula:

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

Which I found here:
http://www.mrexcel.com/forum/showthread.php?t=371766

Hope that helps...

 

[aavvijit]

Dear Colin,

Excellent, Hope my association with your forum will be really enchanting experience.

Thanks, Abhijit