Separating data into muliple columns.

[exflyer1996]

What is the best way to separate the data in this column? It has-

1) Transaction Decription,
2) Amount,
3) Balance,
4) Some type of reference,

<TABLE style="WIDTH: 345pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=460 x:str><COLGROUP><COL style="WIDTH: 345pt; mso-width-source: userset; mso-width-alt: 16822" width=460><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 345pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17 width=460>SALDO FINAL 72.209,98 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" class=xl22 height=17>TARIFA ENVIO DE ESTADO DE CUENTA POR COR 1,40 72.209,98 0716</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" class=xl22 height=17>TARIFA MANTENIMIENTO DE CUENTA 4,50 72.211,38 0915</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" class=xl22 height=17>CHEQUE PAGADO 1.700,00 72.215,88 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" class=xl22 height=17>DEPOSITO EN CHEQUE 1.571,93 73.915,88 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" class=xl22 height=17>LIBERAR CHEQUE BM DEPOSIT. 314,16 72.343,95 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" class=xl22 height=17>CHEQUE PAGADO 1.280,00 72.658,11 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" class=xl22 height=17>DEPOSITO EN EFECTIVO 210,00 73.938,11 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" class=xl22 height=17>DEPOSITO EN EFECTIVO 1.804,50 73.728,11 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" class=xl22 height=17>DEPOSITO EN CHEQUE 140,00 71.923,61 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" class=xl22 height=17>CHEQUE PAGADO 500,00 71.783,61 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" class=xl22 height=17>CHEQUE PAGADO 400,00 72.283,61 0600</TD></TR></TBODY></TABLE>

 

[prajul89]

Try TextToColumns.
with delimiters as (space)

 

[exflyer1996]

I would, but the transaction description that starts the column has spaces in it. If I could use a space break starting from the right, I think that would work. I don't know if that LEN formula does that or not.

 

[prajul89]

Even I don't know what can be done.
I am trying something let me see.

 

[prajul89]

=LEFT(A1,MIN(IFERROR(FIND(0,A1),LEN(A1)),IFERROR(FIND(1,A1),LEN(A1)),IFERROR(FIND(2,A1),LEN(A1)),IFERROR(FIND(3,A1),LEN(A1)),IFERROR(FIND(4,A1),LEN(A1)),IFERROR(FIND(5,A1),LEN(A1)),IFERROR(FIND(6,A1),LEN(A1)),IFERROR(FIND(7,A1),LEN(A1)),IFERROR(FIND(8,A1),LEN(A1)),IFERROR(FIND(9,A1),LEN(A1)))-2)



=RIGHT(A1,LEN(A1)-LEN(G1)-1)


the first formula in G column
the second formula in H column

it will at least separate your transaction Description form the rest of the data and then you can use text to column for the rest of the data

 

[prajul89]

I hope you are using 2007