Text to Columns formula or code

[SlightlyClueless]

Hello,

I have a long list of data from our online system (except from that entire data set below) that needs to be separated out. Each entry that was made in the system had compiled itself into one entry instead of individual ones. In order to correct this, I need to separate out each entry from the cell (a1, a2, a3, etc). Is there a formula or VBA code that I could use to pull each entry into a column for that row? For instance what is contained in cell a1 below would become. Almost all entries lead with a time/date & user stamp.

B1	C1	D1	E1
10/30/2023 10:52AM User123 Inquiry Followup Email	10/27/2023 11:43AM User123 Q3 Marketing Email	10/27/2023 11:42AM User123 Inquiry Followup Email	New Sites Marketing Email

I initially tried using text to columns using a hard return as the delimiter but would up having the time date stamp as one column, the notes as an additional, the hard return separating the notes as another column.



Screenshot

 

[MountainFog]

Wrap your formula in a text join

 

[Fluff]

Wrap your formula in a text join

Isn't that doing to opposite of what the OP is asking for?

 

[Fluff]

What version of Excel are you using?

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’)

Can you post some sample data rather than an image.
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.

 

[MountainFog]

I failed to complete my thought and realize now what I was thinking would essentially undo the initial action.

If you have access to Array Formulas, the following should work.

=TEXTSPLIT(A1,"

")

 

[DanteAmor]

If you have mso365:

=TEXTSPLIT(A1,CHAR(10)&CHAR(10))

 

[DanteAmor]

If you no have 365.
Try the following formula in B2 and copy it to the right:

Dante Amor
	A	B	C	D	E	F
1
2	10/30/2023 10:52AM User123 Inquiry Followup Email 10/27/2023 11:43AM User123 Q3 Marketing Email 10/27/2023 11:42AM User123 Inquiry Followup Email New Sites Marketing Email	10/30/2023 10:52AM User123 Inquiry Followup Email	10/27/2023 11:43AM User123 Q3 Marketing Email	10/27/2023 11:42AM User123 Inquiry Followup Email	New Sites Marketing Email
Hoja1

Cell Formulas
Range	Formula
B2:F2	B2	=TRIM(MID(SUBSTITUTE($A2,CHAR(10)&CHAR(10),REPT(" ",500)),(COLUMNS($B1:B1)-1)*500+1,500))

 

[SlightlyClueless]

If you no have 365.
Try the following formula in B2 and copy it to the right:

Dante Amor
	A	B	C	D	E	F
1
2	10/30/2023 10:52AM User123 Inquiry Followup Email 10/27/2023 11:43AM User123 Q3 Marketing Email 10/27/2023 11:42AM User123 Inquiry Followup Email New Sites Marketing Email	10/30/2023 10:52AM User123 Inquiry Followup Email	10/27/2023 11:43AM User123 Q3 Marketing Email	10/27/2023 11:42AM User123 Inquiry Followup Email	New Sites Marketing Email
Hoja1

Cell Formulas
Range	Formula
B2:F2	B2	=TRIM(MID(SUBSTITUTE($A2,CHAR(10)&CHAR(10),REPT(" ",500)),(COLUMNS($B1:B1)-1)*500+1,500))

Thank you Dante. This worked perfectly!