Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel guru required

Options
  • 03-08-2010 9:00am
    #1
    iMax
    Closed Accounts Posts: 3,489 ✭✭✭


    Guys,

    Urgent question. I have a spreadsheet with approx 8,000 entries of names & addresses which were pasted in without any formatting.

    This means there are some cells which have first & last name in the same cell as well as other detail like complete address in the same cell etc. In some rows where this happens I have blank cells.

    There's no header row which I want to put in & I need to have the data corrected.

    Is there a script or function which can do this for me ? I'm on Excel 2008 BTW.

    EXAMPLE:
    Joe Bloggs	101 Anystreet	Any Place	90210	
Jo	Globbs	101 Anystreet any Place			90210
JOE	Schmoe		102 Anystreet		90210


    So I need to have Firstname | Surname| Building Number | Street 1 | Street 2 etc.

    Every record has these details, they're just not formatted correctly.

    I won't be able to check back in until later but thanks in advance if anyone can help me out.


Comments

  • Options
    #2
    fcrossen
    Registered Users Posts: 241 ✭✭fcrossen


    As long as the ORDER of the entries is correct you should be able to use the "Text to Columns" tool to separate the fields.

    However this will not fix problems like typos and missing fields. You'll need to do these by hand. However as long as you are reasonable (and I don't mean guru) at Excel you should do this quite quickly.

    How messed up is the data (roughly what percentage of rows have typos and/or incorrect count of fields)? That will dictate how long the cleanup exercise will take.


  • Options
    #3
    yeahme
    Closed Accounts Posts: 124 ✭✭yeahme


    I have just learned text to column, a brilliant time saver,
    however if there is some mistakes with typos etc,
    I find its easier to do the text to column wizard one by one so I can go over the info as I do it.
    So start with comma, finish that, look over info then correct.
    then semi colon, finish that, look over info, then correct.
    and so on...
    If you have a lot of info and you do it all in one go with the text to column wizard it takes a lot longer to go thru than comma, finish, semicolon finish, space finish,
    Just my 2cents worth.


  • Options
    #4
    iMax
    Closed Accounts Posts: 3,489 ✭✭✭iMax


    Thanks guys. Sorted it out. Gonna take some time but should save me loads of time in total.


  • Options
    #5
    amen
    Registered Users Posts: 2,781 ✭✭✭amen


    silly question but do you have access to the original data source (where they were copied/pasted from) ?
    might be quicker to reexport


  • Options
    #6
    iMax
    Closed Accounts Posts: 3,489 ✭✭✭iMax


    Hi amen,
    No, don't have access to it, it originally (I think) came from a website


  • Advertisement
Advertisement