What is the formula to change cell formats from "112234501" to look like "
112-23-4501"

Custom Format as
###-##-####

"Darryl" wrote:

What is the formula to change cell formats from "112234501" to look like "
112-23-4501"

Format, Cells, Number tab, Special, SSN

Darryl wrote:

What is the formula to change cell formats from "112234501" to look like "
112-23-4501"

I have two columns that should have the same numbers. One columns is
formatted like "112234501" and the other "112-23-4501" I am trying to find
the rows that do not match. So, I want to create a formula in a third column
to show cells in rows that do not match. When I change the format as you
suggest, the true value remains 112234501. And, 112234501 (minus)
112-23-4501 cannot be calculated. Can you help?

"Sheeloo" wrote:

Custom Format as
###-##-####

"Darryl" wrote:

What is the formula to change cell formats from "112234501" to look like "
112-23-4501"

If the nos like 112-23-4501 are in Col B (and numbers to compare in Col A)
then in
C1 enter
=(SUBSTITUTE(B1,"-",""))*1

Now you can use something like
=IF(A1=C1,"Same","Different") in D1 to compare

Copy down the formulae to the end of your data set


"Darryl" wrote:

I have two columns that should have the same numbers. One columns is
formatted like "112234501" and the other "112-23-4501" I am trying to find
the rows that do not match. So, I want to create a formula in a third column
to show cells in rows that do not match. When I change the format as you
suggest, the true value remains 112234501. And, 112234501 (minus)
112-23-4501 cannot be calculated. Can you help?

"Sheeloo" wrote:

Custom Format as
###-##-####

"Darryl" wrote:

What is the formula to change cell formats from "112234501" to look like "
112-23-4501"

You can check to see if the number 112234501 appears in the other column as text
(112-23-4501) by using:

=isnumber(match(text(a1,"000-00-0000"),b:b,0))

Where A1 holds a number and column B holds the text values.

You could use the same kind of thing to see if the text values show up in the
number column:

=isnumber(match(--substitute(b1,"-",""),a:a,0))

The =substitute() will remove the hyphens, but return text values
The -- will coerce the text values to a real number.

Darryl wrote:

I have two columns that should have the same numbers. One columns is
formatted like "112234501" and the other "112-23-4501" I am trying to find
the rows that do not match. So, I want to create a formula in a third column
to show cells in rows that do not match. When I change the format as you
suggest, the true value remains 112234501. And, 112234501 (minus)
112-23-4501 cannot be calculated. Can you help?

"Sheeloo" wrote:

Custom Format as
###-##-####

"Darryl" wrote:

What is the formula to change cell formats from "112234501" to look like "
112-23-4501"

--

Dave Peterson

thanks much

"Sheeloo" wrote:

If the nos like 112-23-4501 are in Col B (and numbers to compare in Col A)
then in
C1 enter
=(SUBSTITUTE(B1,"-",""))*1

Now you can use something like
=IF(A1=C1,"Same","Different") in D1 to compare

Copy down the formulae to the end of your data set


"Darryl" wrote:

I have two columns that should have the same numbers. One columns is
formatted like "112234501" and the other "112-23-4501" I am trying to find
the rows that do not match. So, I want to create a formula in a third column
to show cells in rows that do not match. When I change the format as you
suggest, the true value remains 112234501. And, 112234501 (minus)
112-23-4501 cannot be calculated. Can you help?

"Sheeloo" wrote:

Custom Format as
###-##-####

"Darryl" wrote:

What is the formula to change cell formats from "112234501" to look like "
112-23-4501"