Excel

[norie]

I formatted cells as SS# but the formula bar does not reconize the dashed
there for I am unable to do a Vlookup from it. What do I need to do to get
the formula bar to reconize the dashed. What can I do otherwise to get the
Vlook up to work.
Thanks,

 

[Gary''s Student]

VLOOKUP() without the "dashes". For example, if E1 thru F4 contains:

123-45-6789 Jim
123-45-6790 Joe
123-45-6795 John
123-45-6799 Janet

then

=VLOOKUP(123456795,E1:F4,2) displays John

 

[norie]

What I am actually wanting is the dashes to show up in the formula bar as
they do in the cells for the ss# so when I search for that number by a V
lookup I can find it one list has the dashed and shows up one does not. I
have to format the cells to SS# and once I do that they do not show the dash
in the formula bar .... then I am unable to do the VLookup formula because
the row I am looking up has the dashes.
I am unable to remove the dashes as it changes the entire sheet removing the
zeros out of the SS#
Thanks

 

[Gary''s Student]

Your approach is correct. Right now you have two flavors of ss#'s:

1. ssn# with real dashes - dashes appear in both the cells and formula bar
2. ssn# by format only - the dashes appear only in the cell and not the
formula bar

You need to use a common format.

Say we have a column of values (column E) like 123456789 that only look like
123-45-6789.

In another cell (say Z1) enter:
=LEFT(E1,3) & "-" & MID(E1,4,2) & "-" & LEFT(E1,4) and copy down

Z1 also displays 123-45-6789, but the dashes are real.

Finally copy column Z and paste/special/values back onto column E.