 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008GETTING RID OF LEADING HASH MARKS
While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that has the list of values to be looked at has '12345 so it is not recognized and I get NA. The table I am working with has the ' attached to all of the entries in that column. Is there any way I can get rid of the hash mark so that my lookup will recognize the vale? > While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that > has the list of values to be looked at has '12345 so it is not recognized > and I get NA. The table I am working with has the ' attached to all of the > entries in that column. Is there any way I can get rid of the hash mark so > that my lookup will recognize the vale? One way is to use TEXT(A1,"@") as the lookup_value in the VLOOKUP function instead of A1 when the lookup_value is in A1. BTW it's an apostrophe ('), not a hash. Ken Johnson Worked great, thanks!!! > > While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that > > has the list of values to be looked at has '12345 so it is not recognized [quoted text clipped - 8 lines] > > Ken Johnson > Worked great, thanks!!! > [quoted text clipped - 10 lines] > > > Ken Johnson You're welcome CLEM. Thanks for the feedback. Ken Johnson Thye say if you can't bring Muhamed to the mountain, bring the mountain to Muhamed. The hash mark is a single quote used to make the cell string data instead of a number. Why don't you look up the number with the quote. from =VLookup(A1, A1:D100,3) =VLookup("'" & A1, A1:D100,3) The first three character are Double Quote Single Quote Double Quote Basically the change adds a single quote to the beginning of A1. I simply add the single quote to the search string rather than remove it from the lookup table. > While attempting to use VLOOKUP THE LOOKUP VALUE IS 12345 the column that > has the list of values to be looked at has '12345 so it is not recognized > and I get NA. The table I am working with has the ' attached to all of the > entries in that column. Is there any way I can get rid of the hash mark so > that my lookup will recognize the vale? Thank you this worked great also........ > Thye say if you can't bring Muhamed to the mountain, bring the mountain to > Muhamed. The hash mark is a single quote used to make the cell string data [quoted text clipped - 19 lines] > > entries in that column. Is there any way I can get rid of the hash mark so > > that my lookup will recognize the vale?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.