Excel Database

adamibbo

adamibbo

adamibbo

Associate
Joined
4 Jul 2007
Posts
367
Location
At my house
Guys I wonder if any you guys had an answer to a problem we've been having.

We have been sent a database of our customers and their requirements who have been onto our website in a .csv file. In one of the cells per line, all the data we need is on several lines and includes many spaces and lines between.

What we need to be able to do is for example get the customer name and email address out of the cell data and paste it into another without having to do it manually as there are over 7000 lines in the csv file.

Does anybody have any ideas?

Cheers

Adam
 
Select Column with data in, then go to the menu bar at the top and click:

Data - Text to columns

edit - then select 'Delimited' and chose the 'delimiters' that you wish to be split up. Eg if you tick 'space' then every 'space' character will result in a new column being used.
 
div0 said:
Select Column with data in, then go to the menu bar at the top and click:

Data - Text to columns

edit - then select 'Delimited' and chose the 'delimiters' that you wish to be split up. Eg if you tick 'space' then every 'space' character will result in a new column being used.
Click to expand...

Hi

Cheers for the quick reply but I can only get this to work for the top line.It splits up "Below is the information supplied by a visitor completing the contact us request form" fine in the columns but stops after that.

The information below is an example of how it looks inside an excel cell.

Below is the information supplied by a visitor completing the contact us request form

on the **** website. It was submitted by L****** H******* (**@**********.co.uk)

on Friday, June 8, 2007 at 06:23:15

---------------------------------------------------------------------------



Name: L***** H*******



Email: **@***********.co.uk



Phone number: *** **** ****



Address: 28 ******* ****

******

******

*** ***


---------------------------------------------------------------------------
 
Robbie G said:
Is there definitely data in column A?

Try selecting more the columns before performing the delimiting.

I.e. highlight columns A - K or something then do it again.
Click to expand...

Yeh theres definately data as shown above ^^ and it won't let me select more than one column as this error occurs.

Microsoft excel can only convert one column at a time. The range can be many rows tall but no more than one column wide.......
 
Could you post a screenshot of the first couple of rows of the spreadsheet?

Feel free to blank out any details, but it would help to see exactly how the data is laid out in Excel.
 
div0 said:
Could you post a screenshot of the first couple of rows of the spreadsheet?

Feel free to blank out any details, but it would help to see exactly how the data is laid out in Excel.
Click to expand...

Hi mate

Image as asked :) basically the whole of the B column is full of these, just want to get the Name and Email addy into other cells. Cheers for the help all

 
edit - actually it might be a bit more complicated. I'll see what I can think of.
 
Last edited:
Ok I think you can do it this way.

Select Column B.

Go to Text to Columns and select Other - in the box press 'Ctrl+j'. Then tick the 'Treat Consecutive delimeters as one' box and press Finish.

You will then end up with a cell (F2?) which has 'Name: L***** H*******'. Cell G2 as 'Email: **@***********.co.uk' and cell H2 as 'Phone number: *** **** ****'.

The address will be broken up across 3 cells I2, J2, K2, L2. You can rejoin these cells using the CONCATENTATE() function if you want.

Hope this helps.

It will probably involve some manual effort to tidy up. If you need to do it a lot of times, I would recommend looking into Macros instead.
 
You must log in or register to reply here.
Back
Top Bottom