+ Reply to Thread
Results 1 to 15 of 15

Delete cells that contain text

  1. 11-14-2010, 10:30 PM #1
    ChrisKader
    ChrisKader is offline
    Registered User
    Join Date
    10-13-2010
    Location
    Malvern, AR
    MS-Off Ver
    Excel 2003
    Posts
    49

    Delete cells that contain text

    Hey guys,

    I have some data that I exported and I have excel parsing it. I am using Text2Columns, but what happens is some employees have there first, middle, and last name in the report. This makes everything on that row shift over by 1 cell to the right. I am wanting to have excel look at one column, find the cells that contain text (A-Z, a-z, etc), delete those cells and shift the cells left. Is that possible?


    This forum has been critical to me getting a HUGE project completed for my job. I would like to think everyone who has helped me so far. Thank you!
    Last edited by ChrisKader; 11-15-2010 at 08:20 PM.
    Reply With QuoteRegister To Reply
  2. 11-15-2010, 12:51 AM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Delete cells that contain text

    Hello Chris,

    Can you provide some examples or possibly the workbook?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 11-15-2010, 01:14 AM #3
    chris0374
    chris0374 is offline
    Registered User
    Join Date
    05-12-2010
    Location
    MD, United States
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Delete cells that contain text

    I am not sure how to shift the cells left but I do have an idea on how to delete cells that have content in them. Let's say you have content in Cell C1 down to C30 (or whatever). You could use a counter like...
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 11-15-2010 at 03:05 AM. Reason: Removed quote and added code tags
    Reply With QuoteRegister To Reply
  4. 11-15-2010, 11:33 AM #4
    ChrisKader
    ChrisKader is offline
    Registered User
    Join Date
    10-13-2010
    Location
    Malvern, AR
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: Delete cells that contain text

    chris0374,

    Thanks for the example. I see what I can do with it.

    Leith Ross,

    I am going to complie a workbook with dummy data and upload so you can see what I mean.
    Reply With QuoteRegister To Reply
  5. 11-15-2010, 11:38 AM #5
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Delete cells that contain text

    Hello Chris,

    Thanks, that will be a big help.
    Reply With QuoteRegister To Reply
  6. 11-15-2010, 02:00 PM #6
    ChrisKader
    ChrisKader is offline
    Registered User
    Join Date
    10-13-2010
    Location
    Malvern, AR
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: Delete cells that contain text

    Hey Lieth Ross,

    I ran into an issue creating the sheet. I guess I am not allowed to do it. So, let me "reword" my question.

    I have x number of rows with data, then a 2 blank rows, then more rows filled with stuff I dont need. How would I go about deleting those rows that have data I do not need?
    Reply With QuoteRegister To Reply
  7. 11-15-2010, 02:19 PM #7
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Delete cells that contain text

    Hello Chris,

    I am trying to visualize how your data looks using the Text to Columns. What delimiter or delimiters are you using to separate the data? How does a name only add 1 column?
    Reply With QuoteRegister To Reply
  8. 11-15-2010, 02:34 PM #8
    ChrisKader
    ChrisKader is offline
    Registered User
    Join Date
    10-13-2010
    Location
    Malvern, AR
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: Delete cells that contain text

    I use a Space as the delimiter. The name issue comes from some people have 3 names in the system, instead of 2. And since I have to use the space delimiter, that 3rd name is put into a coulmn. I do not need the 3rd name, but I need what is supposed to be there which is a number. But that gets bumped over to the right 1 column.
    Reply With QuoteRegister To Reply
  9. 11-15-2010, 03:20 PM #9
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Delete cells that contain text

    Hello Chris,

    So, it would be something like this?
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 11-15-2010, 05:08 PM #10
    ChrisKader
    ChrisKader is offline
    Registered User
    Join Date
    10-13-2010
    Location
    Malvern, AR
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: Delete cells that contain text

    Yep, pretty much.
    Reply With QuoteRegister To Reply
  11. 11-15-2010, 05:48 PM #11
    ChrisKader
    ChrisKader is offline
    Registered User
    Join Date
    10-13-2010
    Location
    Malvern, AR
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: Delete cells that contain text

    Correction: Column C will only have numbers. I want to delete only cells in Column C that have text.
    Reply With QuoteRegister To Reply
  12. 11-15-2010, 06:05 PM #12
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Delete cells that contain text

    Hello Chris,

    Change the column in the macro below to the column you need to check.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 11-15-2010, 06:09 PM #13
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Delete cells that contain text

    deleting rows that contain text in column C:

    Please Login or Register  to view this content.

    Reply With QuoteRegister To Reply
  14. 11-15-2010, 08:19 PM #14
    ChrisKader
    ChrisKader is offline
    Registered User
    Join Date
    10-13-2010
    Location
    Malvern, AR
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: Delete cells that contain text

    Leith Ross,

    You are a god. It worked, like a charm. I had to change a few variables to work with my sheet, but it worked. Do you happen to have an MSN Live ID I can add to my list?
    Reply With QuoteRegister To Reply
  15. 11-15-2010, 08:57 PM #15
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Delete cells that contain text

    Hello Chris,

    Glad it worked. Sorry, I don't have a MSN Live account. I have Google Chat and AIM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1