Social Security Numbers...

aboybilly · Apr 25th, 2002, 01:15 PM

I have 2 seperate excel files (first,a test- applicant registration file and second, their test score file)...I want to use vlookup to merge data from one file to another(their address, etc to match with scores for a full report). I want to use social security number for the lookup because several applicants have the same name. However, in the registration file I used hyphens(-) between the digits, and in the score file the SSNs are simple a string of digits. I cannot get vlookup to accept this -even when I format the cells to add a hyphen(-) to the simple string SSNs. Excel only places the hyphen at the surface, but does NOT recognize the SSN with them to match the other file. Any hints? Thanx in advance!

Mark W. · Apr 25th, 2002, 01:28 PM

Use...

=VLOOKUP(TEXT(A1,"000-00-0000"),Registration!...)

Or...

=VLOOKUP(SUBSTITUTE(A1,"-","")+0,Scores!...)

[ This Message was edited by: Mark W. on 2002-04-25 12:30 ]

Jay Petrulis · Apr 25th, 2002, 01:29 PM

Hi,

1. Use =SUBSTITUTE(A1,"-","") to remove the hyphens from the data. If these are text strings, copy a blank cell and paste special (Values/Add) to the data

2. Format the data as SSN (Format>Cells>Number>Category=Special and choose SSN

3. Do your VLOOKUP

HTH,
Jay

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 01:15 PM	#1
aboybilly New Member Join Date: Apr 2002 Posts: 2	I have 2 seperate excel files (first,a test- applicant registration file and second, their test score file)...I want to use vlookup to merge data from one file to another(their address, etc to match with scores for a full report). I want to use social security number for the lookup because several applicants have the same name. However, in the registration file I used hyphens(-) between the digits, and in the score file the SSNs are simple a string of digits. I cannot get vlookup to accept this -even when I format the cells to add a hyphen(-) to the simple string SSNs. Excel only places the hyphen at the surface, but does NOT recognize the SSN with them to match the other file. Any hints? Thanx in advance!

Apr 25th, 2002, 01:28 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Use... =VLOOKUP(TEXT(A1,"000-00-0000"),Registration!...) Or... =VLOOKUP(SUBSTITUTE(A1,"-","")+0,Scores!...) [ This Message was edited by: Mark W. on 2002-04-25 12:30 ]

Apr 25th, 2002, 01:29 PM	#3
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, 1. Use =SUBSTITUTE(A1,"-","") to remove the hyphens from the data. If these are text strings, copy a blank cell and paste special (Values/Add) to the data 2. Format the data as SSN (Format>Cells>Number>Category=Special and choose SSN 3. Do your VLOOKUP HTH, Jay