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

1. ## Extracting certain text/number from distinct patterns of alphanumeric strings

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.  Reply With Quote

2. 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  Reply With Quote

3. Originally Posted by NBVC 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.  Reply With Quote

4. 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)  Reply With Quote

#### Posting Permissions

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