Extract text only

data was in d9 for me

=SUBSTITUTE(D9,RIGHT(D9,1),"")

Arts said:

This has been driving me crazy !!!

I have a list of countries with a number on the end (the number is always a single digit)

Ireland1
Uk3
Belgium6

What I am hoping for is a formula to remove the number and just return the text, Ireland, Uk etc. There is no space after the country.

Click to expand...

This should do it...

=LEFT(A1,LEN(A1)-1)

Thank you both !

Just as I thought it was only ever going to be one digit after the country France12 gets thrown up....

Is it possible for a formula that can be provided that can "future proof" it as such should I have a country with three digits or four that only the text will be returned. (bearing in mind to include countries with one digit, so it could be a combination of Holland12, Italy2 etc.

Thank you
Arts

Arts said:

Just as I thought it was only ever going to be one digit after the country France12 gets thrown up....

Is it possible for a formula that can be provided that can "future proof" it as such should I have a country with three digits or four that only the text will be returned. (bearing in mind to include countries with one digit, so it could be a combination of Holland12, Italy2 etc.

Click to expand...

This will work no matter how many digits are on the end...

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

skills

Rick Rothstein said:

This will work no matter how many digits are on the end...

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

Click to expand...

That is ridiculous!! (In a good way, amazing!!!) a part of me wants to ask you to break that down so I can perhaps even attempt to understand what is going on but on the surface of it, it seems incomprehensible. (am surprised a formula like that is required for something that seems relatively well I say easy but something that requires just text to be extracted)

But thank you for the formula!

Arts

Rick Rothstein said:

This will work no matter how many digits are on the end...

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

Click to expand...

Arts said:

That is ridiculous!! (In a good way, amazing!!!) a part of me wants to ask you to break that down so I can perhaps even attempt to understand what is going on...

Click to expand...

The FIND function's first argument is an array of constants (each possible digit) which FIND iterates through one at a time (because it is an array) and returns an array of values of the position in the text where it found each digit. We have FIND search A1&"0123456789" instead of just A1 because that makes sure FIND finds each digit (otherwise the array it returns would include error values). The MIN function examines the array of position values returned by the FIND function and returns the smallest one... remember, these are position numbers it is examining, so the smallest one corresponds to the position number of the first digit it found, no matter what that digit was. The LEFT function simply uses that position number for the first digit, subtracts one from it (so that that first digit is not included) and returns the rest (which is all the text in front of the first digit as requested).

Thank you for the break down Rick or Lord Rick I should say... I shall try to digest, Thank you once again !