SPLIT DATA FROM ONE COLUMN INTO ONE COLUMN

[enkenmendoza]

Hi I have a problem fixing this one, How can I separate and spread them into different columns?

|06.01.20201100000005Ppay-CashAdv-StafCtr11114240010.000.00|
|06.01.20201100000005ZeroBalance39999999990.000.00|
|06.01.20201100000005ZeroBalance39999999990.000.00|
|06.01.20201100000005RlzdFXLossAR/AP74001000000.000.00|

Expected Output:

Date	TransNo	Name	Code	Debit	Credit
|06.01.20201	100000005	Ppay-CashAdv-Staf	111142400	0.00	0.00
|06.01.2020	1100000005	ZeroBalance	3999999999	0.00	0.00
|06.01.2020	1100000005	ZeroBalance	3999999999	0.00	0.00
|06.01.2020	1100000005	RlzdFXLossAR/AP	7400100000	0.00	0.00

[Qiu]

@enkenmendoza 
Can you confirm the red 1 should not go to right column?

|06.01.20201

100000005

[enkenmendoza]

Hi Yes, The red digit is suppose to not be included in the right column. Thanks for asking :), It should be "2020" ONLY

[enkenmendoza]

Hi Yes, The red digit is suppose to not be included in the right column. Thanks for asking :), It should be "2020" ONLY

[mot · Accepted solution]

Hi @enkenmendoza,

Please see attached.

I hope this helps. Best!

[Qiu · Accepted solution]

@enkenmendoza 

We could improve the RegEx with your real 

[enkenmendoza]

Thank you for this. It works! but how about different number of values in one column. 

For example:

|06.01.20201100000005Ppay-CashAdv-StafCtr11114240010.000.00|
|06.01.20201100000005ZeroBalance39999999990.000.00|
|06.01.20201100000005ZeroBalance39999999990.000.00|
|06.01.20201100000005RlzdFXLossAR/AP74001000000.000.00|

|06.01.20200184174JANUARY 2020_RENT (MGR)_SWX NEWTOWN2500234843Rent-External5700000000223,572.000.00|
|06.01.2020C53055424101182500234844Input VAT1111484004277.920.00|

Expected Output:

Date	RefNo	Text	TransNo	Name	Code	Debit	Credit
|06.01.2020			1100000005	Ppay-CashAdv-Staf	111142400	0.00	0.00
|06.01.2020			1100000005	ZeroBalance	3999999999	0.00	0.00
|06.01.2020			1100000005	ZeroBalance	3999999999	0.00	0.00
|06.01.2020			1100000005	RlzdFXLossAR/AP	7400100000	0.00	0.00
|06.01.2020	0184174	JANUARY 2020_RENT (MGR)_SWX NEWTOWN	2500234843	Rent-External	5700000000	223,572.00	0.00
|06.01.2020	C5305542410118		2500234844	Input VAT	1111484004	277.92	0.00

I really have a hard time separating the data. and segregate them into their designated fields. I hope you could help me with this one. Thanks

[enkenmendoza]

Thank you for this. It works! but how about different number of values in one column. 

For example:

|06.01.20201100000005Ppay-CashAdv-StafCtr11114240010.000.00|
|06.01.20201100000005ZeroBalance39999999990.000.00|
|06.01.20201100000005ZeroBalance39999999990.000.00|
|06.01.20201100000005RlzdFXLossAR/AP74001000000.000.00|

|06.01.20200184174JANUARY 2020_RENT (MGR)_SWX NEWTOWN2500234843Rent-External5700000000223,572.000.00|
|06.01.2020C53055424101182500234844Input VAT1111484004277.920.00|

Expected Output:

Date	RefNo	Text	TransNo	Name	Code	Debit	Credit
|06.01.2020			1100000005	Ppay-CashAdv-Staf	111142400	0.00	0.00
|06.01.2020			1100000005	ZeroBalance	3999999999	0.00	0.00
|06.01.2020			1100000005	ZeroBalance	3999999999	0.00	0.00
|06.01.2020			1100000005	RlzdFXLossAR/AP	7400100000	0.00	0.00
|06.01.2020	0184174	JANUARY 2020_RENT (MGR)_SWX NEWTOWN	2500234843	Rent-External	5700000000	223,572.00	0.00
|06.01.2020	C5305542410118		2500234844	Input VAT	1111484004	277.92	0.00

I really have a hard time separating the data. and segregate them into their designated fields. I hope you could help me with this one. Thanks