Results 1 to 4 of 4

Thread: Extracting certain text/number from distinct patterns of alphanumeric strings

  1. #1

    Extracting certain text/number from distinct patterns of alphanumeric strings



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi internet people.

    I have a series of data with three distinct patterns which I would like to pull into a more usable format to be able to sort into similar data types.
    A sample string is below:

    17 Flower Store
    17 Flower store 85
    Flower Store 21 Inc
    17 Flower store 21 Inc
    17 Flower store 21 Inc 46
    Flower Store 85

    If the data starts with a number I would like to keep that number as part of the final list:
    ie 17 Flower store would remain 17 Flower Store.
    If the data ends with a number i would like to truncate that number up to the text: 17 Flower Store 85 becomes 17 Flower Store, similarly Flower Store 85 would become Flower Store.
    Finally, I have some data which is embedded within a series text. Given that number is part of the name of the establishment I would like to keep it as part of the text.
    Ie: 17 Flower Store 21 Inc would stay 17 Flower Store 21 Inc, while 17 Flower store 21 Inc 46 would become 17 Flower store 21 Inc

    These steps wouldn't necessarily have to occur in one column. I would prefer a non VBA solution, but could probably use one as well
    I'm familiarizing myself with these constituent excel functions and would love an explanation of how you figured out how to do it and which functions you would use should I encounter a similar issue in the future.

    Thank you so much for your help.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Assuming data starts in A2, try in B2:

    =IF(ISNUMBER(MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)+0),TRIM(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)),A2)

    copied down


  3. #3
    Quote Originally Posted by NBVC View Post
    Assuming data starts in A2, try in B2:

    =IF(ISNUMBER(MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)+0),TRIM(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)),A2)

    copied down

    Thank you so much.
    That fixed like 90% of my issues.

    After applying the formula I realized there were two other methods of organizing the data which occur in a couple of instances and mess up this formula.

    On of them is the deliniating number appearing after a hash tage: ie flower shop #9 needing to be made flower shop
    Another instance is a sub category of data grouped using a dash sign.

    IE. Flower Shop T-1982 needing to just be Flower Shop

    Is it possible to re-work this formula so that these types of information would be removed.
    Alternately, what would a VBA solutoin look like?

    Thanks so much for you help, I really appreciate it.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Try this:

    =IF(LOOKUP(9.999999999E+307,("0"&MID(MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255)&"0123456789")),ROW($1:$30)))+0)>0,TRIM(LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)),A2)


Posting Permissions

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