removing ' before zero

  • Thread starter Thread starter Brian P.
  • Start date Start date
B

Brian P.

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

Try running this Macro:

Sub RemoveIt()
For Each cell In Selection
cell.NumberFormat = "@"
cell.Value = cell.Value
Next cell
End Sub


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
You're very welcome. I've never had to use it myself - just passing on what
I learned here.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top