Help with text to columns

[exflyer1996]

I would like to separate the data below into columns. You have the description, transaction amount, resulting balance, and transaction type code.

<TABLE style="WIDTH: 307pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=409 x:str><COLGROUP><COL style="WIDTH: 307pt; mso-width-source: userset; mso-width-alt: 14957" width=409><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 307pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=409>SALDO FINAL 69.886,80 0999</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>CHEQUE PAGADO 3.560,00 69.886,80 0600</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>LIBERAR CHEQUE BM DEPOSIT. 622,16 73.446,80 0621</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>LIBERAR CHEQUE BM DEPOSIT. 1.710,00 74.068,96 0621</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>DEPOSITO EN EFECTIVO Y CHEQUE 4.130,00 75.778,96 0700</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>DEPOSITO EN EFECTIVO 2.870,00 71.648,96 0700</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>DEPOSITO EN EFECTIVO 330,00 68.778,96 0700</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>DEPOSITO EN CHEQUE 1.468,99 68.448,96 0700</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>CHEQUE DE COMPENS. 700,00 66.979,97 0640</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>CHEQUE DE COMPENS. 945,00 67.679,97 0640</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>CHEQUE DE COMPENS. 6.100,00 68.624,97 0640</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>CHEQUE PAGADO 1.050,00 74.724,97 0600</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>LIBERAR CHEQUE BM DEPOSIT. 613,00 75.774,97 0621</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>LIBERAR CHEQUE BM DEPOSIT. 935,42 76.387,97 0621</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>LIBERAR CHEQUE BM DEPOSIT. 800,00 77.323,39 0621</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>DEPOSITO EN EFECTIVO 210,00 78.123,39 0700</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>LIBERAR CHEQUE BM DEPOSIT. 1.263,84 77.913,39 0621</TD></TR></TBODY></TABLE>

 

[niceguy_jay]

try to put header in the above data as it is not clear

 

[exflyer1996]

There was not one, but these things are-

Description,
Transaction amount,
Balance,
Transaction Code

 

[Andrew Poulsom]

Formulas:

B1 =SUBSTITUTE(A1," "&TEXT(C1,"#,##0.00")&" "&TEXT(D1,"#,##0.00")&" "&TEXT(E1,"0000"),"")
C1 =-LOOKUP(1,-RIGHT(SUBSTITUTE(A1," "&TEXT(D1,"#,##0.00")&" "&TEXT(E1,"0000"),""),ROW($1:$99)))
D1 =-LOOKUP(1,-RIGHT(SUBSTITUTE(A1," "&TEXT(E1,"0000"),""),ROW($1:$99)))
E1 =-LOOKUP(1,-RIGHT(A1,ROW($1:$99)))

works for all except the first entry.

 

[exflyer1996]

Thanks, I'll try it.

 

[ka_123!]

there is very simple way....
select data > go to data tab> click text to column>press next>select space>next.....finish....

 

[Andrew Poulsom]

there is very simple way....
select data > go to data tab> click text to column>press next>select space>next.....finish....

That will put:

DEPOSITO EN EFECTIVO Y CHEQUE

in 5 columns. The poster wants the data parsed int only 4 columns - Description, Transaction Amount, Balance and Transaction Code.