Separate out comma delimited text string via formula

spydey

Active Member
Joined
Sep 19, 2017
Messages
309
Office Version
  1. 2013
Platform
  1. Windows
I have a text string that I need to separate out via formula. I know how to do it via the built in Delimiter, but I have to do this via formula. VBA is an option too ....
The sub-strings are delimited by a comma and a space, e.g. ", "
The individual sub-strings can range from 2 characters to 3 characters.
There is a total possible count of 6 different sub-strings and thus 5 comma/spaces.
However, the full text string can range from one substring (with no comma/space) up to the max 6 sub-strings (with a comma/space separating each one).
The order of the sub-strings varies, so any given substring could be either 3 characters or 2 characters. It is dynamic.
The primary text string populates into a single cell on sheet 2.

I need to take that text string, and break it out into 6 different cells, each one representing one of the sub-strings.
The first cell would contain the substring before the first comma/space.
The 2nd cell would contain the substring after the 1st comma/space and before the 2nd comma/space.
The 3rd cell would contain the substring after the 2nd comma/space and before the 3rd comma/space
.....
The 6th cell would contain the substring after the 5th comma/space
The sub-string cells will be horizontal & on sheet 1.

I can get the first substring with one formula, and the last substring with a different formula.
But due to the dynamic nature of the primary text string, I cannot figure out a single formula that will allow me to separate the substrings from the primary text string, based upon the comma/space delimiter, regardless of how many comma/space delimiters there are.

Do I need to have a different formula in each of the 6 substring cells, with each one slightly adjusted to only grab a certain section of the primary text string????

Any ideas? Thoughts? Suggestions?

Your assistance is much appreciated!!

-Spydey
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,434
Office Version
  1. 365
  2. 2010

spydey

Active Member
Joined
Sep 19, 2017
Messages
309
Office Version
  1. 2013
Platform
  1. Windows
@kweaver

That appears to have worked!! Thank you so much for your assistance. I did change the (B$1-1) to the iteration number of the actual delimiter, i.e. 0, 1, 2 ... 5.

Thanks a million!

-Spydey
 

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,049
Members
414,117
Latest member
marblepoint

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top