Text to columns with multiple spaces

[bieske22]

Hello everyone,

I have a XLS file containing data from my banking account. I can use text to columns to split the cells into multiple columns. The data is split by a "," but the last cell (description) uses multiple spaces instead of ",".

For example:

Account number	CUR	Transaction date	Begin	End	Transaction	Description
123456789	EUR	20192811	5,650.00	5,350.00	-300.00	SEPA 964789784 BIC: BANKUS2K NAME: J. Morrison Description: Cash managment
987654321	USD	20192811	15,321.00	15,471.00	150.00	SEPA 546848 BIC: BANKUS3A NAME: L. Jefferson Description: Salary

Is there a way to extract the last column into multiple columns using a condition like: when there is more then one space: split data into more columns. So: SEPA|964789784|BIC: BANKUS2K|NAME: J. Morisson|Description: Cash|

Thank's in advance!

PS if the spaces are not visible in the above example, please view the attached image.

 

[Fluff]

Judging by that data you can use both comma & space as the delimiters, just make sure you check the "Treat consecutive delimiters as one" box

 

[DanteAmor]

Try this formula in H2 Drag to the right and down

Book1
	G	H	I	J	K	L
1
2	SEPA 964789784 BIC: BANKUS2K NAME: J. Morrison Description: Cash managment	SEPA	964789784	BIC: BANKUS2K	NAME: J. Morrison	Description: Cash managment
3	SEPA 546848 BIC: BANKUS3A NAME: L. Jefferson Description: Salary	SEPA	546848	BIC: BANKUS3A	NAME: L. Jefferson	Description: Salary
Hoja4

Cell Formulas
Range	Formula
H2:L3	H2	=TRIM(MID(SUBSTITUTE(" " & $G2," ",REPT(" ",100)),COLUMNS($H$1:H1)*100,100))

 

[DanteAmor]

Ensures that there are 2 blank spaces between the quotes

=TRIM(MID(SUBSTITUTE(" " & $G2," ",REPT(" ",100)),COLUMNS($H$1:H1)*100,100))

 

[DanteAmor]

Agrr the editor removes the 2 spaces. Another try:

=TRIM(MID(SUBSTITUTE("  " & $G2,"  ",REPT(" ",100)),COLUMNS($H$1:H1)*100,100))