Query Data Format

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

[Paul Sheppard]

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

[Dan Sweetwood]

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