Query Data Format
 

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
--
Dan S

Dan Sweetwood Wrote:
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
--
Dan S

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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=399120

Paul.
Thank you for your response. You have been very helpful.
--
Dan S


"Paul Sheppard" wrote:


Dan Sweetwood Wrote:
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
--
Dan S

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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=399120