I need a formula to a) find the first numeral in a text string b) remove that numeral and all the characters after that numeral

[Cozkincaud]

Hi,

I need help to erase all characters after the first numeral in a text cell, including the first numeral itself. Unfortunately, I only have the Microsoft Ofice Professional Plus 2021 version of Excel. So I don't have functions like TextAfter or RegEx.

Examples of the cells I want to edit are:

Alexander Harman 20 13 4

John Coterell es 1 6 8 I 4

John Chapleyn, John Long, 4 shillings

John Ketton 5 shillings

Syr Antony Wyngffold knyght 500 16 13 4

John Reve 6 shillings

William Lely 6 shillings

the cell "John Ketton 5 shillings" needs to become just "John Ketton"

There is no regularity in either the numerals, the text after the first numeral and the length of this text.

I've Googled for solutions to no avail. I've found a couple of formulas like:

=LEFT(D983, FIND(MIN(IF(ISNUMBER(MID(D983, SEQUENCE(LEN(D983)), 1)), MID(D983, SEQUENCE(LEN(D983)), 1))), D983) - 1) Result is: #VALUE!
=TRIM(MID(D984,FIND(" ",D984,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},D984),""))),LEN(D984))) Result is: #VALUE!
=TRIM(MID(D985,FIND("",D985,MIN(IFERROR(FIND(0,D985),LEN(D985)),IFERROR(FIND(1,D985),LEN(D985)),IFERROR(FIND(2,D985),LEN(D985)),IFERROR(FIND(3,A1),LEN(A1)),IFERROR(FIND(4,D985),LEN(D985)),IFERROR(FIND(5,D985),LEN(D985)),IFERROR(FIND(6,D985),LEN(A1)),IFERROR(FIND(7,D985),LEN(D985)),IFERROR(FIND(8,D985),LEN(D985)),IFERROR(FIND(9,D985),LEN(D985)))),999)) Result is: #VALUE!
=LEFT(D986, FIND({0,1,2,3,4,5,6,7,8,9}, D986&"0123456789") - 1) Result is: #SPILL!

but none of them have worked on my text strings. I've also thought about using regular find and replace.

I tinkered with using the wild card for any numeral 0 to 10 ("#[0-9]") with find and replace (ctrl + F). I was hoping I could add the wildcard for all characters (*) to "#[0-9]" as in: "#[0-9]"*. My assumption was that "#[0-9]"* would find all cells containing a numeral followed by text of any length and kind e.g. "laborers, 1 each". I could then leave Replace empty thus erasing the numeral and every character after it. For some reason ctrl + F would not accept "#[0-9]" as a search term.

Any suggestions would be greatly appreciated.

My thanks for your help and suggestions in advance

 

[Peter_SSs]

I only have the Microsoft Ofice Professional Plus 2021 version of Excel

Please put that in your profile so that helpers always know. Cick your user name at the top right of the forum, then ‘Account details’. (Don’t forget to scroll down & ‘Save’)

Try this

25 02 10.xlsm
	A	B
1	Alexander Harman 20 13 4	Alexander Harman
2	John Coterell es 1 6 8 I 4	John Coterell es
3	John Chapleyn, John Long, 4 shillings	John Chapleyn, John Long,
4	John Ketton 5 shillings	John Ketton
5	Syr Antony Wyngffold knyght 500 16 13 4	Syr Antony Wyngffold knyght
6	John Reve 6 shillings	John Reve
7	William Lely 6 shillings	William Lely
Before digit

Cell Formulas
Range	Formula
B1:B7	B1	=TRIM(LEFT(A1,MIN(FIND(SEQUENCE(10)-1,A1&SEQUENCE(10)-1))-1))

 

[Cozkincaud]

Works like a charm.

Thanks for your help. I very much appreciate it.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Any chance you could now do the profile thing I've mentioned 3 times?