Pulling apart lengthy text data to segregate into smaller sets

[Mike_Alex]

Have a conundrum that am hoping to resolve without a very long written calc including a boat load of Finds, Lens, Rights, Lefts & Concats. Problem is that I have list of item groups that includes partial sets of numbers enclosed within forward slashes. Example would be (A123-45/56/67/78/B123-12) all in one cell. I want to create a cubeset out of this data, so I need the data to return as (A123-45) (A123-56) (A123-67) (A123-78) (B123-12), with each parenthesis being one cell each. Not all item groups have the same number of items, some have one (A123-12) where others may have more (like the aforementioned). Any chance you could think of a smaller formula than the one I am getting ready to start putting together? I am sure 8192 is not going to large enough for this one.

[bgoree09]

Good afternoon,

This is not much help, but it may trim your formula a bit. You can use a series of substitute functions to change all of the slashes to different characters. I use CHAR(2) and (3), but I've never had to parse out more than that. Doing this you could convert that string to:

(A123-45[char(2)]56[char(3)]67[char(4)]78[char(5)]B123-12)

Then you can parse between char(2) and char(3) instead of a giant find statement.

Hope this helps,

[Mike_Alex]

This seems like it would still end up in a lengthy right/left formula tying it all together. There just seems like there should be a more simple way to do it.