Replace Leading Apostrophes

I have a macro that's not doing all it's supposed to be doing.
What it's supposed to do is remove all leading apostrophes.
What it's doing is removing leading apostrophes from numbers, so '507 becomes 507, what I want.
What it's NOT doing is removing leading apostrophes from text, so 'Doc becomes Doc, which I also want.

What do I have wrong?

Please Login or Register  to view this content.

Maybe this:

Please Login or Register  to view this content.

Nope, that only worked on the numbers, same as my original macro.

I wonder if the code is doing anything you think it is. I added a Stop statement to the loop

Please Login or Register  to view this content.

and set watches for Len(s.value) and Instr(s.Value,"'"). Len() returns the string length without the apostrophe, and Instr() returns 0, indicating that the apostrophe character was not found in the string. I think your mistake is assuming that the apostrophe character is being seen as part of the stored text value. It appears to me that VBA is not seeing the apostrophe at all, so there is nothing happening in the Replace function. In the case of numbers stored as text, when VBA writes the number as text back to Excel, Excel decides to convert the number as text to a number.

A little bit of further testing. I note that if I enter "Dog" into a blank cell, there is no leading apostrophe in the formula bar. If I add a leading apostrophe ("'Dog"), then the leading apostrophe is there, but the text otherwise behaves like "Dog" (LEN()=3 in both cases, for example). However, once I have entered the leading apostrophe, I cannot delete the apostrophe. Having once had the apostrophe entered, Excel seems to remember that text in this cell wants a leading apostrophe in the formula bar.

Curious behavior that I have never explored before. The leading apostrophe does not appear to actually be part of the cell's value, but Excel is still aware that a cell has had a leading apostrophe in the past. From there, it needs more exploration to understand exactly what is happening.

Can you move a step back and explain what you are trying to do here? Convert numbers stored as text to numbers? Is the leading apostrophe creating some other problem?

Thanks for looking deeper. The only "problem" is Text numbers when I need real numbers, but an "annoyance" is where I have apostrophes show up and don't need them. But I also noticed the persistence issue, so maybe I'm looking for something I can't do.

Also, I often get data that's a mix of both types, like

Please Login or Register  to view this content.

some of which has apostrophes and some doesn't. So I'd like to be able to just do away with any apostrophes and let the numbers be numbers and the text be text.

I tend to use the Text to Columns command for something like that.

Ok, try this:

Please Login or Register  to view this content.

It will, however, clear other formatting. Note that .PrefixCharacter is a Read-only Property.

Hmmm, that works, but it's kind of strange. Does a fine job on the text items, and it takes away the apostrophe from the numbers, but still leaves them as text numbers. Even if I copy them and paste on a new sheet as values, they're still text numbers. Easy enough to convert, but I haven't seen that before.

Thanks for pointing me in the right direction. I beefed it out a little so my numbers are numbers. I think this is going to work for me.

Please Login or Register  to view this content.

I have seen lots of examples on the forum of data that looks, to all intents and purposes as numeric, but presents and acts as though it is text. And, to be honest, I have no idea why it happens.

Maybe try:

Please Login or Register  to view this content.

Originally Posted by jomili

Thanks for pointing me in the right direction. I beefed it out a little so my numbers are numbers. I think this is going to work for me.

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.

I guess we crossed, I didn't see your amended "Test" macro. wow, does what I want and much shorter! Thanks!

Originally Posted by jomili

I guess we crossed, I didn't see your amended "Test" macro. wow, does what I want and much shorter! Thanks!

Yep, guess we did. Enjoy!