Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: Deleting part of a cell

  1. #11
    Board Regular
    Join Date
    Jan 2003
    Location
    Alaska
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Could I use this type of formula to delete the dashes from a column of social security numbers?

    Example: 123-45-6789 turned into 123456789.

    The original cells are text cells, so I need to make the program recognize that they're numbers. The normal formatting of cells functions do not work.

  2. #12
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why would you want to change them? You will have a problem with any SSN that begins with one or more 0.
    Curiosity may have killed the cat but...
    satisfaction brought it back. >^;^<

  3. #13
    Board Regular
    Join Date
    Jan 2003
    Location
    Alaska
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to validate them versus another column of SSN's using a Countif formula. However, as this column is considered text (not numbers), the Countif won't work.

  4. #14
    Board Regular
    Join Date
    Jan 2003
    Location
    Alaska
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    But you're right, the deletion of leading zeros is what makes this a tricky issue....

  5. #15
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Count works with numbers while Counta works with text but countif works with both text and values. What type of formula are you using?
    Curiosity may have killed the cat but...
    satisfaction brought it back. >^;^<

  6. #16
    Board Regular
    Join Date
    Jan 2003
    Location
    Alaska
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's a very simple: countif(a:a, b2) where column A is filled with SSN's that are numbers and column B has what looks to be numbers but is actually text such as (123-45-6789). It seems that if the dashes are typed, rather than placed there by the format cells function, the program doesn't think it's really a number.

    I could use the countif for two columns of SSN as text...or two columns of SSN as numbers, but not a combination, it seems.

  7. #17
    Banned
    Join Date
    Apr 2006
    Posts
    2,328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =COUNTIF(A:A,LEFT(B1,3)& MID(B1,5,2)&RIGHT(B1,4))

  8. #18
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think you would be wiser if you converted your numbers back to SSN. Do you have ASAP utilities? There are several routines in there that would do the trick for you.
    Curiosity may have killed the cat but...
    satisfaction brought it back. >^;^<

  9. #19
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is another option for you.
    Assume values in column D and text in column E
    =COUNTIF(E:E,TEXT(D5,"000-00-0000"))
    Curiosity may have killed the cat but...
    satisfaction brought it back. >^;^<

  10. #20
    Banned
    Join Date
    Apr 2006
    Posts
    2,328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It might be wiser first to check that column A consists solely of numbers and column B consists solely of text.
    (And then try my formula.)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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