Convert Bank Statement to Excel

[nehpets12]

How can I split this into columns

This is the columns I would like please

Date of transaction Date entered Description Amount £


13 JUNE 14 JUNE PAYPAL *AQUACADABRA 35314369001 39.90
13 JUNE 14 JUNE IDEN PARK SERVICE TONBRIDGE 4.19
15 JUNE 17 JUNE WWW.PARKMOBILE.COM INTERNET 3.40
17 JUNE 18 JUNE TOPS PIZZA COM LONDON 22.18
29 MAY 24 JUNE ENTERPRISE RENT A CAR MAIDSTONE GBR 50.00 CR

Any help would be fantastic

 

[Joe4]

Is that really the format you are working with (a single space between every word/amount)?
Do you have any ability to choose a different file export format?
Most banking software that I have used offer different export formats, i.e. Excel, CSV, Tab-Delimited Text File, etc.
If you have the ability to choose one of those options, the data with be much easier to work with, and you could use the Import Wizard or Text to Columns on the data in CSV or Tab-Delimited format.

 

[Special-K99]

Here's one way
With the data in A1

in B1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," CR","")," ","#",2)," ","#",3)

in C1
=(LEN(B1)-LEN(SUBSTITUTE(B1," ","")))

in D1
=SUBSTITUTE(B1," ","#",C1)

Now use Text to Columns with a delimiter character of #

NOTE: This removes the CRedit found on row 5 of your example data

 

[nehpets12]

Thats Perfect thank you very much for your help

 

[MickG]

I see you have a result but here's another option for data starting "A2" and results starting "B2".

[COLOR="Navy"]Sub[/COLOR] MG06Aug43
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    nStr = ""
    Sp = Split(Dn.Value, " ")
        ReDim ray(1 To 4)
         ray(1) = Sp(0) & " " & Sp(1): ray(2) = Sp(2) & " " & Sp(3)
         
         n = UBound(Sp)
         [COLOR="Navy"]Do[/COLOR] Until IsNumeric(Sp(n))
            nStr = Sp(n) & nStr
            n = n - 1
        [COLOR="Navy"]Loop[/COLOR]
          
        ray(4) = Sp(n) & " " & nStr
        
        [COLOR="Navy"]For[/COLOR] Ac = 4 To n - 1
            ray(3) = ray(3) & " " & Sp(Ac)
         [COLOR="Navy"]Next[/COLOR] Ac
        
        [COLOR="Navy"]With[/COLOR] Dn.Offset(, 1).Resize(, 4)
           .Value = ray
           .Columns.AutoFit
        [COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]Next[/COLOR] Dn

Regards Mick

 

[nehpets12]

Wow Thanks that works perfectly too thank you so much for your time helping me

 

[MickG]

You're very welcome , Thanks for the feedback