Convert to number to remove apostrophe 1

[Clotty]

Background
I'm using vlookup to pull in some data from another spreadsheet, but the numeric key that I'm using has the "'" (left aligned format character) in one spreadsheet, but not in the other. Format Cells... shows both sets of numbers are in "General" format. The result is that the vlookup fails as, not surprisingly, it can't match the keys.

This is not a problem manually, because the very helpful Excel 2002 is giving me the option to convert my range to number format with one click. However, I want to do this in VBA.

Problem
I've tried doing a record macro while I use the convert to number option, but it doesn't record anything. I've also tried:
Range("A5:A20&quot.Select
Selection.NumberFormat = "General"

but the "'" remains.

Any help gratefully received.
tia
Chris

 

[JohnAcc]

To convert data to a long data type try:

NewData = Clng(Expression)

 

[Bowers74]

Select the range that contains the numbers w/ apostraphies and run this macro (copy this code to a standard code module)

Sub LoseApostrophy()
Dim sel As Range
Dim c As Range
Set sel = Selection
If TypeName(sel) <> &quot;Range&quot; Then Exit Sub
For Each c In sel
    If c.HasFormula Then GoTo Line1
    c.Value = c.Value
Line1:
Next c
End Sub

This will remove all of the apostraphies for you and the cells format will automatically be changes to "General".

Hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Clotty]

John/Mike
Thanks for your superfast responses. I've used the LoseApostrophy sub and it worked first time. My vlookups are now happy
Thanks again
Chris

 

[Bowers74]

Clotty,

since you're new, I should inform you that the traditional way of thanking someone that has helped you is to award them a star (if you found thier post helpful). To award a star click the Mark this post as a helpful/expert post! to the bottom left side of the post that you found helpful.

Thanks!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!