Why Apostrophes Don't Appear in Cell??

**CrazyFileMaker** · 04-23-2009, 02:01 AM

Hello,

My question is about reading an Apostrophe/Single Quote to a cell.

My program reads a text file a single character at a time. And many lines of the text I'm reading begin with a single quote.

Unlike double quotes and other characters, single quotes aren't showing up in cells when they are read in first. They show up only in the function box. This is a huge problem for my program.

The following code produces the correct results:

If Character = "A" Then
ActiveSheet.Cells(1,1).Value = ActiveSheet.Cells(1,1).Value + Character

This correctly writes the letter A to the first cell.

However, the following code:

If Character = (Chr$(39)) Then
ActiveSheet.Cells(1,1).Value = ActiveSheet.Cells(1,1).Value + Character

... Doesn't write the single quote to the cell when it's the first character in the cell. It only writes the single quote to the function box. This is a huge problem because when i try to append the next character to the value of the cell holding the single quote, the single quote is entirely omitted and it never appears in the result.

Is there a workaround for this? How can I write a single quote to a cell and have it appear there when the next character is appended to it?

Thanks for any and all help.

**DonkeyOte** · 04-23-2009, 02:22 AM

Not entirely sure why/what you're doing but you may find that you're better off adding Chr(39) twice over at the end of processing all other chrs... so once you've processed the string do a test of the left most char and if it's chr39 repeat it twice, eg:

'completed processing string from 2nd char to the end
Character = Left(your string,1)
Cells(1,1).Value = IIf(Character = Chr(39),"''",Character) & Cells(1,1).Value

If you're looking to strip certain chars in a string you may find regular expressions a better / faster alternative to your current method... not for the feint hearted mind -- I'm still getting an idea of the basics myself but you'll find NBVC etc have posted quite a few examples - for more info see: http://www.regular-expressions.info/

**Mallycat** · 04-23-2009, 02:26 AM

the single apostrophe is short hand for text in a cell. Just like the + or = is the shorthand for a formula. That is why you have a problem. If you need to put the apostrophe in, use the formula =char(39)

**CrazyFileMaker** · 04-23-2009, 02:36 AM

Thanks Donkey. That's a good idea.

Would you or someone tell me, are there other characters that don't appear in cells when written to them first? I want to account for other characters that might be of equal surprise.

**DonkeyOte** · 04-23-2009, 02:40 AM

Not that I can think of - other than 0 of course if the entire string is numeric and the cell is not formatted to Text... that's not to say there aren't any ...

**Mallycat** · 04-23-2009, 02:45 AM

Originally Posted by CrazyFileMaker

Would you or someone tell me, are there other characters that don't appear in cells when written to them first?

see my post above + and = if they are first and don't form working formulae, you will get an error. unless you import it as text

**CrazyFileMaker** · 04-23-2009, 02:58 AM

Thanks Donkey and MallyCat. That's helpful.

MallyCat. I see what you're saying about =Char(39). If I type =Char(39) into a cell, is there anyway to append other strings to that value? When I enter letters after that, like "=Char(39) Apple" i get the message: "The formula you typed contains an error"

If i could somehow append other letters to that value, that would work for sure. Is that possible?

**Mallycat** · 04-23-2009, 03:01 AM

There is always a way. It just depends on how easy the solution is. This one is easy - use the ampersand character like this

=char(39) & " Apple"

leaving spaces inside the quotes where you need them