How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Zabo04 Your Own Question
Zabo04
Zabo04, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 283
Experience:  Experienced in Word, Excel, Access, Powerpoint, and Outlook.
58597962
Type Your Microsoft Office Question Here...
Zabo04 is online now
A new question is answered every 9 seconds

I copied and pasted my old Bank of America statement.

Customer Question

I copied and pasted my old Bank of America statement. Unfortunately in Excel it looked like this in one column:
date
vendor
amount
there are 108 entries covering 326 rows. I want them to convert to look like this:
date | vendor | amount in three columns x 108 rows. Help!
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Zabo04 replied 1 year ago.

Have you tried "Text to Columns" in the Data ribbon (at least in Excel 2007 and 2010). Select the column with the data, check "Delimited", then press next. Now I am not sure from your description, but there should be a space comma or tab between the data columns. Check the correct delimiter then press finish. Let me know if that works or not.

Expert:  Zabo04 replied 1 year ago.

If you have a different version of excel or that does not work let me know. If you could attach an excel spreadsheet (using the paperclip icon above where you are typing) with two or three examples (obviously you want to protect your information, so replace some of the fields, but spacing and all formatting is critical to keep the same so I can see the format).

Customer: replied 1 year ago.
I have Office 10, do not know about . . . "Have you tried "Text to Columns" in the Data ribbon (at least in Excel 2007 and 2010). Select the column with the data, check "Delimited", then press next. Now I am not sure from your description, but there should be a space comma or tab between the data columns. Check the correct delimiter then press finish."slowly, assuming I know nothing.
Customer: replied 1 year ago.
the data was copied from pdf and pasted to Excel, showing up like this:8/25/2014
CHECKCARD 0821 SWEET FROG 200104 WAYNESBORO VA 24431064234200488400051
-8.9
Expert:  Zabo04 replied 1 year ago.

So when you copy the data it should all go into column a. Click on the A and the entire column is highlighted.

Expert:  Zabo04 replied 1 year ago.

Click on Data, this makes the Data ribbon appear. Under data tools there is a Text to Columns button. Click on that.

Expert:  Zabo04 replied 1 year ago.

A message box appears, check the radio button next to Delimited. Then click on Next.

Expert:  Zabo04 replied 1 year ago.

On the next screen on the left side half way down is an option box with the heading "Delimiters" remove all check marks then check only "Space". Click Finish.

Expert:  Zabo04 replied 1 year ago.

All of the data is now split across columns.

Expert:  Zabo04 replied 1 year ago.

For the columns you do not want, Click on the letter at the top with the right mouse button and select delete.

Expert:  Zabo04 replied 1 year ago.

There will be some issues, like Sweet Frog, which has a space in it and will get split over two columns. Because of the way the data is formatted, delimited with spaces and using spaces in the fields, Excel has no way to distinguish, which is why a file would normally use commas to separate columns.

Expert:  Zabo04 replied 1 year ago.

What I would do,in the case I had to vendors like Sweet Frog and Bestbuy, I would select the best buy columns and drag them right so that the right most columns (cost) lines up. Then delete the columns I did not want. Of course do no select any cells for the vendor or further left (so leftclick and hold down on the cells to move, release, left click and drag right on the mouse, an outline of where to data is going will be drawn, move the right edge one column right).

Customer: replied 1 year ago.
You took so long to get back to me that I had gone home for the day. I tried to contact you the next morning without success. I ten got Robert @ JustAnswer to provide the solution.
Expert:  Zabo04 replied 1 year ago.

I apologize for that. I was underway on a submarine. No comms for those few days.

Related Microsoft Office Questions