Splitting Text

[EvonS]

Using Office365

I need a way to split up text like this "Appropriate, sustainable housing, Financial stability" into columns. The problem is that when I use =TEXTSPLIT(A1, ",") it splits up "Appropriate, sustainable housing" in to 2 separate columns when it should be one. I cannot change that text to exclude the comma unfortunately. Is there a way to:

1. Get TEXTSPLIT to ignore the comma after "Appropriate"?
2. Is there a way to get TEXTSPLIT to split based on capital letters?

Note: I can't change the delimiter but I can place each option I want to split in quotes.

Thanks in advance for the help.

 

[Fluff]

Using Office365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

Fluff.xlsm
	A	B	C
1
2	Appropriate, sustainable housing, Financial stability	Appropriate, sustainable housing	Financial stability
Sheet5

Cell Formulas
Range	Formula
B2	B2	=TEXTBEFORE(A2,",",-1)
C2	C2	=TEXTAFTER(A2,", ",-1)

 

[EvonS]

Thanks for responding. I will update my profile. Your formula makes sense for the sample data I provided but I don't think it will work for my use since I can get up to 15 entries separated by commas with entries sometimes having commas within them.

 

[Fluff]

In that case pleas post a few examples of the data you get.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[EvonS]

In that case pleas post a few examples of the data you get.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Unfortunately I can't install XL2BB on my work computer.

Some more examples of data I may get.

• Access to/availability of resources, professional services and social supports, Access to/availability of cultural education, Appropriate, sustainable housing
• Appropriate, sustainable housing, Financial stability
• Access to stable housing, Stability of the family unit, Hospital involvement - Toronto Western Hospital

I can get any combination of the above.

 

[Rick Rothstein]

Give this formula a try...

=LET(c,CHAR(SEQUENCE(26,,65)),TEXTSPLIT(LET(t,REDUCE(A2,c,LAMBDA(a,x,SUBSTITUTE(a,x,x&x))),MID(t,2,LEN(t))),", "&c))

 

[EvonS]

Give this formula a try...

=LET(c,CHAR(SEQUENCE(26,,65)),TEXTSPLIT(LET(t,REDUCE(A2,c,LAMBDA(a,x,SUBSTITUTE(a,x,x&x))),MID(t,2,LEN(t))),", "&c))

Wow almost there. The formula adds extra letters though and I can some value errors. Here's a copy of the current data I'm trying to split.

Housing in close proximity to services

N/A

N/A

N/A

N/A

Access to stable housing, Stability of the family unit, Hospital involvement – Toronto Western Hospital

Primary care physician

Taking prescribed medication

Appropriate, sustainable housing, Financial stability

N/A

Access to/availability of resources, professional services and social supports, Access to/availability of cultural education, Appropriate, sustainable housing

 

[Rick Rothstein]

Wow almost there. The formula adds extra letters though and I can some value errors. Here's a copy of the current data I'm trying to split.

Housing in close proximity to services

N/A

N/A

N/A

N/A

Access to stable housing, Stability of the family unit, Hospital involvement – Toronto Western Hospital

Primary care physician

Taking prescribed medication

Appropriate, sustainable housing, Financial stability

N/A

Access to/availability of resources, professional services and social supports, Access to/availability of cultural education, Appropriate, sustainable housing

You should have posted those examples in your original post so that we would know the extent of what your data looked like. Anyway, give this modified version of my original formula a try...

=LET(c,CHAR(SEQUENCE(26,,65)),TEXTSPLIT(REDUCE(A1,c,LAMBDA(a,x,SUBSTITUTE(a,", "&x,", "&x&x))),", "&c))

 

[EvonS]

You should have posted those examples in your original post so that we would know the extent of what your data looked like. Anyway, give this modified version of my original formula a try...

=LET(c,CHAR(SEQUENCE(26,,65)),TEXTSPLIT(REDUCE(A1,c,LAMBDA(a,x,SUBSTITUTE(a,", "&x,", "&x&x))),", "&c))

Sorry about that. It works perfectly. You mind explaining how it works? Is it splitting by capital letters?

 

[Rick Rothstein]

Sorry about that. It works perfectly. You mind explaining how it works? Is it splitting by capital letters?

First, I replace the combination of a comma/space/capital letter with a comma/space/capital letter/capital letter... then I split on the combination of a comma/space/capital letter. In other words, I double up the letter following a comma space and then split on the combination of a comma/space/capital letter. I need to do that because the delimiter (comma/space/capital letter) will be lost when the split occurs, so one of the two now doubled up letters will now remain after the split.