Extract numbers on the left in a string that also contains numbers on the right

Using Power Query

Excel 2016 (Windows) 64 bit

	A	B	C
2	1069ANDRAA0013		1069
3	A230ANDRAA0013		A230
4	14721ARSECA0181		14721
5	W101HAKEZH5003		W101

Sheet: Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.1", "Column1.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition",{"Column1.1"})
in
    #"Removed Other Columns"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

B1=LEFT(A1,SEARCH({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A1,2)-1)

This is an array formula.

Close with CTRL+SHIFT+Enter.

The brackets appear automaticly.

Another approach.

B1=IF(ISNUMBER(MID(A1,5,1)*1)=FALSE,LEFT(A1,4),LEFT(A1,5))

If you don't mind to use helper cells.

B1
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0"," "),"1"," "),"2"," "),"3"," "),"4"," "),"5"," "),"6"," "),"7"," "),"8"," "),"9"," "))

C1
=LEFT(A1,FIND(MID(B1,IFERROR(FIND(" ",B1)+1,1),1000),A1)-1)

Regards.

Another solution

=LEFT(A1,AGGREGATE(15,6,ROW($A$1:$A$100)/(1/((CODE(MID(A1,ROW($A$1:$A$99),1))<=57)*(CODE(MID(A1,ROW($A$2:$A$100),1))>57))),1))

it support case of
14A707CHEVSC0081
A14A404AHESS123

Regards.

Try

=LEFT(A2)&-LOOKUP(0,-MID(A2,2,{1,2,3,4,5}))