Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Extract text only

  1. #1
    Board Regular
    Join Date
    Sep 2007
    Posts
    487
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Dublin, Ireland
    Posts
    1,421
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text only

    data was in d9 for me

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

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Extract text only

    Quote Originally Posted by Arts View Post
    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular
    Join Date
    Sep 2007
    Posts
    487
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text only

    Thank you both !

  5. #5
    Board Regular
    Join Date
    Sep 2007
    Posts
    487
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Extract text only

    Quote Originally Posted by Arts View Post
    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular
    Join Date
    Jan 2014
    Location
    Dublin, Ireland
    Posts
    1,421
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text only

    skills

  8. #8
    Board Regular
    Join Date
    Sep 2007
    Posts
    487
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text only

    Quote Originally Posted by Rick Rothstein View Post
    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. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Extract text only

    Quote Originally Posted by Rick Rothstein View Post
    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)
    Quote Originally Posted by Arts View Post
    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).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    Board Regular
    Join Date
    Sep 2007
    Posts
    487
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 !
    Last edited by Arts; May 6th, 2015 at 12:45 PM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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