1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Separating data in excel with wierd separator

Discussion in 'Business Applications' started by Berners, Nov 12, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Berners

    Berners Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    16
    I have a spreadsheet of data that has been exported from an access database.
    The address is appearing in one cell and I need to separate it to import into a CRM system.

    I have tried to separate this using "text to columns" but the separator is a little square. This is visible in the "text to columns" dialogue box - but not on the spreadsheet ( or I cannot make it visible) - So I am not able to copy it into the box for "other" separators".

    This is a big spreadsheet - and it would take me ages to separate them all manually.

    Any ideas please ?
     
  2. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Berners
    Can you possibly upload a small example (as you would see it in Excel) - maybe 5 or six records.
    We can then have a look at the structure and come up with the best solution.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Berners, you could also split it in the Access database in a Query or using Visual Basic prior to Exporting.
     
  4. Berners

    Berners Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    16
    Here is one cell
     

    Attached Files:

  5. Berners

    Berners Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    16
    sample posted online
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    In Step 2 of the Text to Columns wizard, click in the "Other" box then type 0010 on the Number keypad while holding down ALT. Nothing will appear to have happened, but click "Next" & you should see the difference.

    HTH

    EDIT: note -- there's something definitely up with your wb. I can't run the "Format Cells" dialog unless I have >1 cell selected. :confused:
     
  7. Berners

    Berners Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    16
    Genius! - thank you very much.

    I just came across this site today and will be telling all my friends and colleagues.

    Many thanks:)
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    WARNING: you may need to do some additional cleaning up. I notice your first "line" in A2 is "Abbey House ", i.e. with a trailing space. If this is common, try the following after you've split your data.

    1. Insert a new column A

    2. Use this in column A:

    =TRIM(B2)

    as far down as you need.

    3. Select (new) column A & copy ; then Edit > Paste Special > Values.

    4. Delete column B.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/650893

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice