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.

 

[Peter_SSs]

Thanks for the follow up Peter!

No problem.

Can you clarify whether you are still using Excel 2016 as per your profile?

 

[Arts]

Hi Peter

Sorry for the very late response, I had booked a day of work and family errands had me running around.

I use excel 2016 at home but the company I am currently working for use excel 365.

 

[Peter_SSs]

I use excel 2016 at home but the company I am currently working for use excel 365.

It might be better to include both versions in your profile and specify in any thread you start which version(s) any suggestion needs to work in.
You haven't actually said here which version you want an answer for, but if it is 365 then you can try this instead.

23 06 29.xlsm
	A	B
1	alan1	alan
2	alan123]	alan
3	simon1	simon
4	simon123	simon
Extract Text

Cell Formulas
Range	Formula
B1:B4	B1	=TEXTBEFORE(A1,SEQUENCE(10)-1)

 

[Arts]

Hi Peter

Firstly thanks for the alternative formula! I didn't think it was possible to update a profile with two versions but admittedly I didn't give this a thorough look over when updating my profile but yes your point is valid I shall include what version of excel I require a solution for.


I think there may have been a misunderstanding, I was more asking for an understanding of the workings of the formula you provided rather than an alternative formula

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.

I wasn't sure how this formula picked knew how to pick up the text irrespective of the number of digits

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

Appreciate your help as always

 

[Peter_SSs]

Although replying to me you had marked an earlier post as the solution so it wasn't clear to me which formula you were asking about.

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

Suppose cell A1 contained "Simon481"

The formula first adds all 10 possible digits at the end so it becomes
"Simon4810123456789"

The FIND then finds the first occurrence of each of the 10 digits in that string
0 occurs first at position 9
1 occurs first at position 8
2 at pos 11
3 at 12
4 at 6
5 at 14
6 at 15
7 at 16
8 at 7
9 at 18

MIN of those positions is pos 6
LEFT(A1,6-1) = "Simon"

 

[Arts]

Although replying to me you had marked an earlier post as the solution so it wasn't clear to me which formula you were asking about.

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

Suppose cell A1 contained "Simon481"

The formula first adds all 10 possible digits at the end so it becomes
"Simon4810123456789"

The FIND then finds the first occurrence of each of the 10 digits in that string
0 occurs first at position 9
1 occurs first at position 8
2 at pos 11
3 at 12
4 at 6
5 at 14
6 at 15
7 at 16
8 at 7
9 at 18

MIN of those positions is pos 6
LEFT(A1,6-1) = "Simon"

Thank you so much for the breakdown of this!! I always find it quite amazing how you guys (MVP's and I'm sure a fair sure of non MVP's who may not have the badge) are able to see how to construct formulas in order to attain the outcomes

 

[Peter_SSs]

You're welcome.

 

[Arts]

Sorry to come back on this and hope my eagerness to learn isn't verging on the annoyance but this should be the final question on this..

On the below is there a reason why the second half of the formula is different, (you mentioned you simplified the formula) why is it in one version there is quotations around the latter part and not in the other, is it simply to do with that one is listed as an array ie within the { } brackets?

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

 

[Peter_SSs]

you mentioned you simplified the formula

I'm withdrawing that statement as the 'simplification' or shortening of the formula is insignificant. Initially I had misinterpreted @etaf's formula and thought that it may not work as-is in Excel 2016 which is why I had asked confirmation about your version. So the formulas are pretty similar but yes, the first one creates an array of values and finds where each of the 10 digits first occurs in the respective array elements whereas the second one creates a single string and finds where each of the 10 digits first occurs in that string.

 

[Arts]

Thanks Peter!