How to dissect cell text at commas and spill the components with sorting and uniform spelling? :)

[Rnkhch]

Hello,

I have some cells that contain long comma-separated text like this in each cell:

Day0, Day 1,Day7, Day 15,Day30, Day 250,Day45, Day125

I want to make a formula that would dissect all components of the cell text (should be maximum 40) between commas and spill the resulting components horizontally. Additionally, it would be great to have the following features (as many as feasible )

1) uniform spelling of all components with 1 space in between "Day" and the number
2) typos in "Day" automatically corrected in the output spill
3) skip the components with missing number (so that they don't appear in the spilled row)
4) sort the spilled results with increasing number

I was having a hard time coming up with the function, so thanks for any input!

Thanks for any input!

 

[Peter_SSs]

With over 250 posts you should by now be getting the idea that one example is really insufficient as it does not demonstrate any variety that may occur in the data. Further, expected results are invaluable in clarifying what you are trying to achieve. Please consider both for future questions.

Based on my reading of your post,..

IF you do have the TEXTSPLIT function

22 08 07.xlsm
	A	B	C	D	E	F	G	H	I
1	Day0, Day 1,Day7, Day 15,Day30, Day 250,Day45, Day125	Day 0	Day 1	Day 7	Day 15	Day 30	Day 250	Day 45	Day 125
2	yaD0, Day 1,Day7, Day 15,Dya,Day30, Day 250,Day45, Day125	Day 0	Day 1	Day 7	Day 15	Day 30	Day 250	Day 45	Day 125
Days

Cell Formulas
Range	Formula
B1:I2	B1	=LET(ch,MID(A1,SEQUENCE(LEN(A1)),1),TEXTSPLIT("Day "&SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(ch+0),ch," ")))," ",",Day "),","))
Dynamic array formulas.

IF you do not have TEXTSPLIT

22 08 07.xlsm
	A	B	C	D	E	F	G	H	I
1	Day0, Day 1,Day7, Day 15,Day30, Day 250,Day45, Day125	Day 0	Day 1	Day 7	Day 15	Day 30	Day 250	Day 45	Day 125
2	yaD0, Day 1,Day7, Day 15,Dya,Day30, Day 250,Day45, Day125	Day 0	Day 1	Day 7	Day 15	Day 30	Day 250	Day 45	Day 125
Days2

Cell Formulas
Range	Formula
B1:I2	B1	=LET(ch,MID(A1,SEQUENCE(LEN(A1)),1),TRANSPOSE(FILTERXML("<p><c>Day "&SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(ch+0),ch," ")))," ","</c><c>Day ")&"</c></p>","//c")))
Dynamic array formulas.

 

[Rnkhch]

Thank you so much! This is exactly what I needed.

I will definitely provide more of both components that you mentioned in future. I've been trying to get my XL2BB to work again. Not sure what happened, but starting a few days ago, Excel blocks it every time I want to start it.

 

[Peter_SSs]

Thank you so much! This is exactly what I needed.

You're welcome. Thanks for the confirmation.

I've been trying to get my XL2BB to work again. Not sure what happened, but starting a few days ago, Excel blocks it every time I want to start it.

Could this be related? XL2BB Disappears when Excel is Reopened
If that is not it, can you describe in more detail what the symptoms are that produce the bolded comment?

 

[Rnkhch]

Thank you! I just tested on both my work and home computers, and interestingly, it happens only on my work computer. After selecting XL2BB from the add-on selection and clicking OK, I get the following two errors:

1) Excel ribbon saying: "SECURITY RISK Microsoft has blocked macros from running because the source of this file is untrusted"
2) error window saying: "Microsoft Office has identified a potential security concern. Microsoft has blocked macros from running because the source of this file is untrusted". And there is a "Disable Macros" button which I didn't try to click.

Perhaps it's an IT-implemented extra security check?

 

[Peter_SSs]

Perhaps it's an IT-implemented extra security check?

It obviously is some security setting.
Are you allowed to run other macros?
Option B in the linked page looks like it could be a solution for you - provided IT allows you to have, and place items in, a 'Trusted Location'.

 

[Rnkhch]

Thanks. I haven't tried other file-based macros like XL2BB. (I've only tried the VBA-based macros which I get from here).

But I'll talk to them during the week to see what I can do, and I'll look into the trusted location that you mentioned. I'll update back.

 

[Rnkhch]

So I was finally assisted today by our IT, and they remotely set up a trusted location for my macros, and now I have full access to XL2BB again . They said they implemented this extra security starting about a couple of weeks ago.

 

[Peter_SSs]

Good news! Thanks for letting us know.