CAN I USE IF FUNCTION OR V LOOK UP

Hi Tania
Try this (it's a bit long winded, but I think it does what you want):

On a blank worksheet, you'll need to use columns A to I:

Put the Column headers in row 1:
Cell A1: Original Data
Cell B1: Put character after the text
Cell C1: Remove duplicate numbers
Cell D1: Date
Cell E1: Details
Cell F1: Amount
Cell G1: Purch
Cell H1: Paypoint
Cell I1: VAT

Paste your data from the bank statement down column A, starting at Cell A2

Put these formulae into the following cells and copy them down their columns:

Cell B2: =TRIM(SUBSTITUTE(REPLACE(A2,1,9,"")," ","~",(LEN(REPLACE(A2,1,9,""))-LEN(SUBSTITUTE(REPLACE(A2,1,9,"")," ","")))-(LEN(REPLACE(A2,1,9,""))-LEN(SUBSTITUTE(REPLACE(A2,1,9,""),".","")))+1))

Cell C2: =IF(LEN(REPLACE(A2,1,9,""))-LEN(SUBSTITUTE(REPLACE(A2,1,9,""),".",""))=1,B2,SUBSTITUTE(B2,RIGHT(B2,(LEN(B2)-FIND(" ",B2,1)+1)),"",1))

Cell D2: =DATEVALUE(SUBSTITUTE(TRIM(SUBSTITUTE(A2,REPLACE(A2,1,9,""),"")),".","/"))

Cell F2: =VALUE(RIGHT(C2,(LEN(C2)-FIND("~",C2))))

Cell G2: =IF(AND(LEFT(C2,FIND("~",C2)-1)<>$H$1,LEFT(C2,FIND("~",C2)-1)<>$I$1)=TRUE,LEFT(C2,FIND("~",C2)-1),"")

Cell H2: =IF(LEFT($B2,LEN(H$1))=H$1,H$1,"")

Cell I2: =IF(LEFT($B2,LEN(I$1))=I$1,I$1,"")

You might want to hide columns B and C once you've put the formulae in.

Good luck

Kathy