I have made some subtotals. Now before some of the (not all) totals there is an ' .For example
1 totals
2 totals
'3 totals
'4 totals
5 totals
etc.

Now it is not possible to use the VLOOKUP function. I want to search on 1,2,3,4,5 (I removed the "totals").

Any solutions????

What's interesting is that if you select one of the cells with the ' in it, but don't edit it, once you move out of the cell, then the number appears appropriately.

If I click the cell (cursor appears after the text) and the press enter. the ' still is before the text. I really have the remove the ' manually.

If you select the cell and then hit F2, it appears to be the same.

Hoever, if you want to remove that you can use something like in C2:

=LEFT(B2,LEN(B2)-7)

Then copy down the column. (The "7" stands for the space plus Totals = " Totals")

Then format the cells as general. It may be at that point that selecting the cell and hitting F2 will change it to the number.

Here's some VBA code I use to remove tick marks.

sub TickRemover()

Range("A1").select

Do
ActiveCell = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""

End Sub

Enjoy!
-Mike

Thanx This works great!!!

To remove the ', couldn't you just have done a copy/Paste Special and multiplied all the cells in the range by 1 to force them to numbers? That's how I do it anyway.


Andrew H