D
Darryl
What is the formula to change cell formats from "112234501" to look like "
112-23-4501"
112-23-4501"
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
Sheeloo
Custom Format as
###-##-####
###-##-####
B
Bob I
Format, Cells, Number tab, Special, SSN
D
Darryl
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?
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?
S
Sheeloo
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
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
D
Dave Peterson
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.
(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.
D
Darryl
thanks much
Sheeloo said: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