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.

Remove Hyphens In Excel Cells - Please Help!!!!

Discussion in 'Business Applications' started by bassinkurt, Feb 15, 2007.

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

    bassinkurt Thread Starter

    Joined:
    Feb 15, 2007
    Messages:
    3
    Here is my issue. I have tens of thousands of columns in excel that include SS# and TIN# and Zip codes - and these are sent to me by customers so an outreach to them all (60K) would be an impossibility. We have set up a file load program to extract data in a CSV format, but it breaks when one of these cells contains hyphens. Is there a way to remove hyphens that are included in the columns of data? Currently, I am just filtering and manually removing them and it is way too time consuming – THANKS!!!
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    have you tried a replace

    EDIT
    Replace
     
  3. bassinkurt

    bassinkurt Thread Starter

    Joined:
    Feb 15, 2007
    Messages:
    3
    Yes, I tried that and it works - great idea! But, the only problem with that, is that it drops the first 0 (zero) in Zip for New England states and in the TIN and SS#. When you run the replace function, it doesn't seem to keep the formatting (text), even though it states that it is the same format (NOT). This is so frustrating - Thanks though.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    must be changing from a text format to a number format - if dropping first 0
    what can you replace with that will not break the CSV format - maybe a ~ or ! or other symbol to keep the text or even replace with a space

    an example spreadsheet would help
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    bassinkurt, you can run a Macro to do this, but we would need an example of the data Structure, i.e. what columns are where.
    Or the data could be Imported in to Access and a "Replace" Query run to remove the Hyphens.
     
  6. bassinkurt

    bassinkurt Thread Starter

    Joined:
    Feb 15, 2007
    Messages:
    3
    My problem is the program built has check digit capabilities so replacing the first 0 would break the file load. One thing I did figure out is that I can view it in note pad, then do the replace and that doesn't knock out the first Zero - PHEW!!!! It is one more step but a heck of a lot shorter than filter/manually removing them.

    I will send you an example a bit later - b/c I am green at working with macros and I used to know Access, but I haven't used it in years (we have a new reporting dept) - Access if not used is lost at least in this guys mind. I need to get into that side of manipulating b/c excel is killing me.

    I want to thank you guys though - with out the replace message - I wouldn't have thought of it in notepad - and right now that is the fix. 490K rows of manipulation today! Over doubled from yesterday!


    Thanks again!
     
  7. 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/544369

  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