jjam103
New member
- Joined
- Aug 12, 2013
- Messages
- 2
- Reaction score
- 0
- Points
- 0
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.
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.