Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel data label format special number characters (part 2) | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
External Data | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Format Data Series Markers | Charts and Charting in Excel |