Thanks:  0
Likes:  0

1. ## Extract text only

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.

Thanks
Arts

2. ## Re: Extract text only

data was in d9 for me

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

3. ## Re: Extract text only

Originally Posted by Arts
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.
This should do it...

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

4. ## Re: Extract text only

Thank you both !

5. ## Re: Extract text only

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

6. ## Re: Extract text only

Originally Posted by Arts
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.
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

8. ## Re: Extract text only

Originally Posted by Rick Rothstein
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)
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

9. ## Re: Extract text only

Originally Posted by Rick Rothstein
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)
Originally Posted by Arts
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...
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).

10. ## Re: Extract text only

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•