Extract Text Only - Formula Help Pls

[Arts]

Hi all

I thought I had this and I'm sure it's really simple but I can't seem to think of the solution

I need to extract the name only (drop the numbers at the end) so I have

Simon1 = Simon
Steve4 = Steve

I had the following formula =LEFT(A2,LEN(A2)-1) but this then fell apart when I came across Alan12

There is no space after the name, I haven't come across a name with three digits yet ie Simon100 but if the formula can be future proofed that would be ideal.

Any help on this is most appreciated as always.

 

[etaf]

how about
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&{0,1,2,3,4,5,6,7,8,9}))-1)

Book10
	A	B
1
2	alan1	alan
3	alan123]	alan
4	simon1	simon
5	simon123	simon
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&{0,1,2,3,4,5,6,7,8,9}))-1)

 

[Sufiyan97]

Try

Book1
	A	B	C
1	Simon01	Simon
2	Steve4	Steve
3	Steve114	Steve
4	Steve11	Steve
5
6
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=LEFT(A1,AGGREGATE(15,6,SEARCH(ROW($1:$10)-1,A1),1)-1)

 

[Arts]

Thanks guys both seem to work, I would never have got there looking at those formula's! Always amazes me how complex it can get suddenly get when an extra digit needs removing (in this case)

I'll mark etaf as the solution simply as it came through first

@Sufiyan97 I did edit your formula where my data starts in row 2 as row 1 is just headers by editing the row number =LEFT(A1,AGGREGATE(15,6,SEARCH(ROW($2:$9),A1),1)-1) but this seem to throw up an error so I left it as 1, assuming I can expand the row if need be ie increase the 9 to 100.

 

[Sufiyan97]

You're welcome.

Actually no need to increase or decrease rows, this will just work as it is.

I have updated the formula incase you need it

=LEFT(A1,AGGREGATE(15,6,SEARCH(ROW($1:$10)-1,A1&ROW($1:$10)-1),1)-1)

 

[Arts]

You're welcome.

Actually no need to increase or decrease rows, this will just work as it is.

I have updated the formula incase you need it

=LEFT(A1,AGGREGATE(15,6,SEARCH(ROW($1:$10)-1,A1&ROW($1:$10)-1),1)-1)

Ah I see, my excel ignorance showing there...where I saw ROW function only going to row 10 assumed it was for the first 10 rows only. Thanks for the reply and updated formula!

 

[etaf]

the row function , produces the numbers to search for
ROW($1:$10) will give 1 to 10 , and then the -1 gives 0 to 9
so a much better way then i posted with the numbers in an array FIND({0,1,2,3,4,5,6,7,8,9}

 

[Arts]

Thanks for the explanation!

 

[Peter_SSs]

so a much better way then i posted

I disagree that either formula suggesting ROW($1:$10) is a better way.

23 06 27.xlsm
	A	B	C
1	Simon01	Simon	Simon
2	Steve4	Steve	Steve
3	Steve114	Steve	Steve
4	Steve11	Steve	Steve
Text

Cell Formulas
Range	Formula
B1:B4	B1	=LEFT(A1,AGGREGATE(15,6,SEARCH(ROW($1:$10)-1,A1),1)-1)
C1:C4	C1	=LEFT(A1,AGGREGATE(15,6,SEARCH(ROW($1:$10)-1,A1&ROW($1:$10)-1),1)-1)

It is not robust. Inserting any rows at the top will likely cause incorrect results to show. Example

23 06 27.xlsm
	A	B	C
1
2
3	Simon01	#NUM!	Simon0
4	Steve4	Steve	Steve
5	Steve114	Steve	Steve
6	Steve11	Steve	Steve
Text

Cell Formulas
Range	Formula
B3:B6	B3	=LEFT(A3,AGGREGATE(15,6,SEARCH(ROW($3:$12)-1,A3),1)-1)
C3:C6	C3	=LEFT(A3,AGGREGATE(15,6,SEARCH(ROW($3:$12)-1,A3&ROW($3:$12)-1),1)-1)

The post #2 formula (which is robust against subsequent row insertion above) can be shortened/simplified a little to ..

23 06 27.xlsm
	A	B
1	alan1	alan
2	alan123]	alan
3	simon1	simon
4	simon123	simon
Text (2)

Cell Formulas
Range	Formula
B1:B4	B1	=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

 

[Arts]

Thanks for the follow up Peter! sorry for the delay in responding work duties

If any one could perhaps shed some light on the workings of the formula that would be great, I will use the evaluate tool to see what I can understand but mainly how the formula seems to work across whether there is 1 one digit after or two.