Separating multiple text values with a single cell

[Blanchetdb]

Hi

I am dealing with a tool that allows an individual to enter multiple text entries and when the info is exported to excel, the info appears all in the same cell ... example: Location, Language, Security

Unfortunately, this does not allow the user of the excel spreadsheet to filter, report or collect statistics using the info from that cell. Is there a formula that can be added to the spreadsheet that would extract each text entry and move each of them to its own cell ..... the formula would read the text until it reaches the comma "," and extract that text and copy it to an empty cell at the end of that row.

example

The extract gives me the first 4 columns
Could I add a formula in the 5th, 6th, 7th, 8th, ... columns that would read column 4, extract the first text up to the comma and insert in that column 5 and continue for column 6 and 7 and 8 ...

Number	Status	Request Type	Request Sub-Type	Sub-Type 1	Sub-Type 2	Sub-Type 3
123568	Completed	Reorganization	Security, Position, Location	Security	Position	Location

any assistance would be greatly appreciated

 

[etaf]

how many text strings could there be ??
are you still using excel 2016
if a more recent version
text split
=TEXTSPLIT(D2,",")

Book2
	A	B	C	D	E	F	G
1	Number	Status	Request Type	Request Sub-Type	Sub-Type 1	Sub-Type 2	Sub-Type 3
2	123568	Completed	Reorganization	Security, Position, Location	Security	Position	Location
Sheet1

Cell Formulas
Range	Formula
E2:G2	E2	=TEXTSPLIT(D2,",")
Dynamic array formulas.

 

[Blanchetdb]

how many text strings could there be ??
are you still using excel 2016
if a more recent version
text split
=TEXTSPLIT(D2,",")

Book2
	A	B	C	D	E	F	G
1	Number	Status	Request Type	Request Sub-Type	Sub-Type 1	Sub-Type 2	Sub-Type 3
2	123568	Completed	Reorganization	Security, Position, Location	Security	Position	Location
Sheet1

Cell Formulas
Range	Formula
E2:G2	E2	=TEXTSPLIT(D2,",")
Dynamic array formulas.

Hi... I just added that to the spreadsheet and it worked perfectly .... thank you

 

[HighAndWilder]

Hi... I just added that to the spreadsheet and it worked perfectly .... thank you

What version of Excel are you using?

 

[Joe4]

Hi... I just added that to the spreadsheet and it worked perfectly .... thank you

If that works, then you most be using a newer version of Excel than 2016, as TextSplit was not available in Excel 2016.
Can you please update your profile to reflect which version of Excel you are currently using?

It will help people assist you in the future to know which version of Excel you are using,

 

[Blanchetdb]

What version of Excel are you using?

I thought I was using 2016 but we may have moved to 365

 

[HighAndWilder]

I thought I was using 2016 but we may have moved to 365

A lot has happened since 2016. There are some amazing functions available now.

 

[etaf]

glad to have helped , i may not have posted the later version function, just a hunch i guess , as you had 2016 in profile, glad its now updated to 365 - so many more functions to make things so much easier to do

 

[Joe4]

You can use this to confirm which version of Office you are using: About Office: What version of Office am I using? - Microsoft Support