Deleting part of a cell

[jaybeen]

Hello,

I want to delete part of a cell in Excel. For example if a cell had the data "BlueGreen" how would I delete the "Blue" bit just to leave the "Green" bit behind? Surely there is an easier way then physically deleting the first part in each cell??? I have about 4000 cells to work on!!
Please help and regards,
Jaybeen.

 

[tape deck]

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.

 

[klb]

Why would you want to change them? You will have a problem with any SSN that begins with one or more 0.

 

[tape deck]

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.

 

[tape deck]

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

 

[klb]

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

 

[tape deck]

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.

 

[Boller]

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

 

[klb]

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.

 

[klb]

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

 

[Boller]

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