Thanks:  0
Likes:  0

# Thread: Deleting part of a cell

1. 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. Why would you want to change them? You will have a problem with any SSN that begins with one or more 0.

3. 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. But you're right, the deletion of leading zeros is what makes this a tricky issue....

5. Count works with numbers while Counta works with text but countif works with both text and values. What type of formula are you using?

6. 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. =COUNTIF(A:A,LEFT(B1,3)& MID(B1,5,2)&RIGHT(B1,4))

8. 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.

9. Here is another option for you.
Assume values in column D and text in column E
=COUNTIF(E:E,TEXT(D5,"000-00-0000"))

10. 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.)

## User Tag List

#### Posting Permissions

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