 |
 | 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 / New Users / September 2007removing ' before zero
Hi there - I copy/paste a few columns from a SQL query in Enterprise Manager. The one of the columns has ID's 6-10 digits long, most simple like 485232189. However, some begin with a zero. So, when I copy/paste this column into excel, it appears like: '048538569 when I just want it to be 048538569 I'm trying to do an index(match()) and I'm getting the #N/A,obviously because the ' is messing things up here. The clean() removes the ' but when I copy/paste value to do the indexing, the ' reappears. Can anyone help me out here? Thanks - Brian You could try -- and I don't know if this will work in your case -- and format the column as Text *before* doing the paste. Though, no matter what, if you want to retain the leading 0 you cannot treat the contents as numeric. To Excel 1, 01, 001, 0001, and so on, are all 1. > Hi there - I copy/paste a few columns from a SQL query in Enterprise > Manager. The one of the columns has ID's 6-10 digits long, most [quoted text clipped - 12 lines] > > Thanks - Brian Hi Brian, Try running this Macro: Sub RemoveIt() For Each cell In Selection cell.NumberFormat = "@" cell.Value = cell.Value Next cell End Sub  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hi there - I copy/paste a few columns from a SQL query in Enterprise > Manager. The one of the columns has ID's 6-10 digits long, most [quoted text clipped - 12 lines] > > Thanks - Brian Sandy - you are awesome. Works like a charm. Thanks so much. You're very welcome. I've never had to use it myself - just passing on what I learned here. SignatureSandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Sandy - you are awesome. Works like a charm. Thanks so much.
|
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.