Delimit formula not working

[Edgee1]

Hi

I need a little help on this one. I am separating (or trying to ) separate data that has come from a SharePoint list where the user can select multiple options. Everywhere I have looked has suggested using this formula

=TRIM(MID(SUBSTITUTE($A1,";#",REPT(" ",99)),COLUMN(A1)*99-98,99))

as my go to to separate the responses across multiple columns, no matter how many times I have tried this and yes I have updated the cell reference so that it points to the cell in my query the formula returns a blank. The user can select up to 13 options (I doubt any will) so I recognise that I will need 13 columns to drop the data into once separate. How the code looks is irrelevant as the worksheet is only a sheet for holding calculations - it is not for presentation. I would prefer that no vba is used on this or if anyone has a suggestion on vba, it is kept simple (it's not my space)

This is an example of how the content in the output worksheet may be presented:

Yes - Childcare/Preschool;#Yes - Public Open Space/Recreation Users;#Yes - Residential - Medium Density;#Yes - Unable to be Determined

 

[Edgee1]

...the perils of doing this on the train

The output is typical of SharePoint using ;# as the separator. In looking at all the forums, all suggest this formula but to no avail - no matter what I try it produces only a blank.

Hopefully someone can help me out here.

 

[jtakw]

Hi,

There is no such thing as a "go to" formula for data extraction from a Text String, these formulas are Not universal, every formula has to be "Custom" built for a specific set of data, but your formula is close.
I wonder what you mean by "it produces only a blank", I tried your formula, it failed after the 3rd separation, but the first 3 worked.

Since you say up to 13 options may be selected, try this:

Book1
	A	B	C	D	E
1	Yes - Childcare/Preschool;#Yes - Public Open Space/Recreation Users;#Yes - Residential - Medium Density;#Yes - Unable to be Determined	Yes - Childcare/Preschool	Yes - Public Open Space/Recreation Users	Yes - Residential - Medium Density	Yes - Unable to be Determined
Sheet92

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(";#"&$A1,";#",REPT(" ",LEN($A1)*13)),COLUMNS($B1:B1)*LEN($A1)*13,LEN($A1)*13))

B1 formula copied across as far as needed.

 

[Edgee1]

Thanks jtakw

By producing a blank - the cell was empty...nothing was showing. But not a problem now as your solution worked sensationally. Thanks so much

 

[jtakw]

You're welcome, welcome to the forum.