Query Data Format

D

Dan Sweetwood

I am importing a simple patient list into Excel that is the data range for a
vlookup formula. The challenge is that the patient RecordID used for the
value to lookup is an autonumber in Access and imports in a format other than
a number that the vlookup formula does not recognize.

RecordID LastName FirstName
24001 Brown John

If I go to the data range and manually place the curser behind each RecordID
"number" and hit delete it then aligns to the right and is recognized as a
number. I want to have the query updated or refreshed at opening of the file
and I do not want to manually reformat. Any suggests as to what the problem
is and how I can best solve it? Your help will be greatly appreciated.

Dan
 
P

Paul Sheppard

Dan said:
I am importing a simple patient list into Excel that is the data range
for a
vlookup formula. The challenge is that the patient RecordID used for
the
value to lookup is an autonumber in Access and imports in a format
other than
a number that the vlookup formula does not recognize.

RecordID LastName FirstName
24001 Brown John

If I go to the data range and manually place the curser behind each
RecordID
"number" and hit delete it then aligns to the right and is recognized
as a
number. I want to have the query updated or refreshed at opening of the
file
and I do not want to manually reformat. Any suggests as to what the
problem
is and how I can best solve it? Your help will be greatly appreciated.

Dan
Click to expand...

Hi Dan

2 suggestions:

1. Highlight the column with the RecordID's in and do Edit > Replace,
replacing space (space bar) with nothing, this will remove the leading
spaces

2. Insert a column next to Record ID and enter this formula =TRIM(A2)
assuming RecordID is in column A and starts in row 2, adjust as
necessary, this will remove the leading space, then Copy column B and
Paste Special Values in column A, delete column B
 
Top