Split Text

[uswyne]

Hi everyone,

I have text file and I want to split on excel sheet, plz help me out to do this.


Excel Sheet Requirement

Date Column	Name Column	Text Column
12/6/22	OMS Irshad Arain	Today I shall be around one hr late due to some personal issues
12/6/22,	Atiq Ahmed	I will come to office around 10 am, after some work at bahria facilitation center

Data File Text
12/6/22, 7:31 AM - OMS Irshad Arain: Today I shall be around one hr late due to some personal issues
12/6/22, 9:27 AM - Atiq Ahmed: I will come to office around 10 am, after some work at bahria facilitation center.
12/6/22, 9:29 AM - OMS Niaz Ahmed: Copy
12/6/22, 9:44 AM - OMS Abu Adil: Okay.
12/7/22, 9:09 AM - OMS Manu Ram: Due to a bank work, will be late by about an hour.
12/7/22, 9:43 AM - OMS Rehan Tariq: Due to some work, will arrive around 11

Thanks in anticipation

 

[JvdV]

I'd look into the PowerQuery add-in. Unfortunately no longer supported nor updated for Excel 2010, but you can still use it I suppose?

Otherwise some formulae might help. Note that below I also included an option for a dynamic array (ms365 users):

Book1
	A	B	C
1	12/6/22, 7:31 AM - OMS Irshad Arain: Today I shall be around one hr late due to some personal issues
2	12/6/22, 9:27 AM - Atiq Ahmed: I will come to office around 10 am, after some work at bahria facilitation center.
3	12/6/22, 9:29 AM - OMS Niaz Ahmed: Copy
4	12/6/22, 9:44 AM - OMS Abu Adil: Okay.
5	12/7/22, 9:09 AM - OMS Manu Ram: Due to a bank work, will be late by about an hour.
6	12/7/22, 9:43 AM - OMS Rehan Tariq: Due to some work, will arrive around 11
7
8	12-6-2022	OMS Irshad Arain	Today I shall be around one hr late due to some personal issues
9	12-6-2022	Atiq Ahmed	I will come to office around 10 am, after some work at bahria facilitation center.
10	12-6-2022	OMS Niaz Ahmed	Copy
11	12-6-2022	OMS Abu Adil	Okay.
12	12-7-2022	OMS Manu Ram	Due to a bank work, will be late by about an hour.
13	12-7-2022	OMS Rehan Tariq	Due to some work, will arrive around 11
14
15	Date Column	Name Column	Text Column
16	12-6-2022	OMS Irshad Arain	Today I shall be around one hr late due to some personal issues
17	12-6-2022	Atiq Ahmed	I will come to office around 10 am, after some work at bahria facilitation center.
18	12-6-2022	OMS Niaz Ahmed	Copy
19	12-6-2022	OMS Abu Adil	Okay.
20	12-7-2022	OMS Manu Ram	Due to a bank work, will be late by about an hour.
21	12-7-2022	OMS Rehan Tariq	Due to some work, will arrive around 11
Sheet2

Cell Formulas
Range	Formula
A8:C13	A8	=CHOOSE(COLUMN(A1),--LEFT($A1,FIND(",",$A1)-1),MID($A1,FIND(" - ",$A1)+3,FIND(": ",$A1)-FIND(" - ",$A1)-3),RIGHT($A1,LEN($A1)-FIND(": ",$A1)-1))
A15:C21	A15	=REDUCE({"Date","Name","Text"}&" Column",A1:A6,LAMBDA(a,b,VSTACK(a,HSTACK(--TEXTBEFORE(b,","),TEXTAFTER(TEXTBEFORE(b,":",2),"- "),TEXTAFTER(b,": ")))))
Dynamic array formulas.

 

[uswyne]

I'd look into the PowerQuery add-in. Unfortunately no longer supported nor updated for Excel 2010, but you can still use it I suppose?

Otherwise some formulae might help. Note that below I also included an option for a dynamic array (ms365 users):

Book1
	A	B	C
1	12/6/22, 7:31 AM - OMS Irshad Arain: Today I shall be around one hr late due to some personal issues
2	12/6/22, 9:27 AM - Atiq Ahmed: I will come to office around 10 am, after some work at bahria facilitation center.
3	12/6/22, 9:29 AM - OMS Niaz Ahmed: Copy
4	12/6/22, 9:44 AM - OMS Abu Adil: Okay.
5	12/7/22, 9:09 AM - OMS Manu Ram: Due to a bank work, will be late by about an hour.
6	12/7/22, 9:43 AM - OMS Rehan Tariq: Due to some work, will arrive around 11
7
8	12-6-2022	OMS Irshad Arain	Today I shall be around one hr late due to some personal issues
9	12-6-2022	Atiq Ahmed	I will come to office around 10 am, after some work at bahria facilitation center.
10	12-6-2022	OMS Niaz Ahmed	Copy
11	12-6-2022	OMS Abu Adil	Okay.
12	12-7-2022	OMS Manu Ram	Due to a bank work, will be late by about an hour.
13	12-7-2022	OMS Rehan Tariq	Due to some work, will arrive around 11
14
15	Date Column	Name Column	Text Column
16	12-6-2022	OMS Irshad Arain	Today I shall be around one hr late due to some personal issues
17	12-6-2022	Atiq Ahmed	I will come to office around 10 am, after some work at bahria facilitation center.
18	12-6-2022	OMS Niaz Ahmed	Copy
19	12-6-2022	OMS Abu Adil	Okay.
20	12-7-2022	OMS Manu Ram	Due to a bank work, will be late by about an hour.
21	12-7-2022	OMS Rehan Tariq	Due to some work, will arrive around 11
Sheet2

Cell Formulas
Range	Formula
A8:C13	A8	=CHOOSE(COLUMN(A1),--LEFT($A1,FIND(",",$A1)-1),MID($A1,FIND(" - ",$A1)+3,FIND(": ",$A1)-FIND(" - ",$A1)-3),RIGHT($A1,LEN($A1)-FIND(": ",$A1)-1))
A15:C21	A15	=REDUCE({"Date","Name","Text"}&" Column",A1:A6,LAMBDA(a,b,VSTACK(a,HSTACK(--TEXTBEFORE(b,","),TEXTAFTER(TEXTBEFORE(b,":",2),"- "),TEXTAFTER(b,": ")))))
Dynamic array formulas.

THANKS FOR YOUR SUPPORT ITS HELPFUL FOR ME