remove apostrophe?

[ielamrani]

I have imported data from another program, and I run into this problem where an apostrophe got added to the beginning of each Cell; because of this I can't format the cells. Anyone has a solution? I am using Excel 2003 Thanks in advance.

[missinglinq]

I'm confused by your post, in that this is a forum for Access questions. Are you importing data from Excel 2003 into Access?

You also don't say where you're trying to do this, but in Access/VBA the general way to strip a leading apostrophe would be

If Left(Me.OriginalCell, 1) = "'" Then
  Me.OriginalCell = Right(Me.OriginalCell, Len(Me.OriginalCell) - 1)
End If
 

Linq ;0)>

[Stewart Ross]

Hi. Linq's solution is valid for VBA and Access. Assuming that this is really an Excel question, the apostrophe is indicating that the values were exported as text (in Excel the use of a leading apostrophe forces interpretation of values as text strings). The apostrophe is not actually treated as part of the string, so removing it is not as straightforward as it could be.

The simplest solution in Excel itself is to leave these values as they are, add a blank column immediately after them and replicate the following value conversion from the top cell down the column:

=Value(CellRef)

where CellRef is the appropriate cell reference for the text string value (A1, C4 or whatever). After doing so, select the new column, use Copy and Paste Special, Values to paste the values into the cells (overwriting the =value() formulas). You can then delete the original (text-exported) columns.

If this process has to be done frequently you can always record the steps as an Excel macro to automate the conversion, or program the conversion directly in VBA.

-Stewart

[NeoPa]

This is actually a lot more complicated than it appears (although, if you can afford to lose any formatting that might be associated with the cells, then Stewart's solution will work well for you). The problem is that the apostrophe is treated in a special way by Excel in that it works like formatting but is stored in the cell in such a way that only the data is seen (Check the .Value, .Text & .Formula properties). The only place I can find the apostrophe is in the cell editing area when the cell is active.

Assuming Stewart's solution is not adequate to your needs, try the following slightly more fiddly one instead which will keep all formatting etc from the original cells (column).

1. Copy the required column (Select at top and Ctrl-C).
2. Insert into next column position (Select next column and, using right-click, choose Insert Copied Cells).
3. Select all cells that you want to change.
4. Type in A (or any basic text) followed by Ctrl-Enter to put this into each selected cell. This clears the cell from wanting to insert the apostrophe.
5. Type in "=CellRef" (Hit "=" key then right-arrow key) and then Ctrl-Enter to enter the modified version of the formula into each selected cell.
6. Copy these cells again (Ctrl-C).
7. Paste / Special / Values (Alt-E,S,V,Enter) to put the changed values back into the selected cells.
8. Lastly, remember to delete the column that was added to leave the data as it was before, except for having removed the apostrophes.

[ielamrani]

I am sorry for not being clear. The data was imported from one excel sheet to another. the apos was used for an invoice number column to show the zeros. I was able to solve it.
Thank you all for your help.

[NeoPa]

It irritated me that I couldn't find a property that was different in the cell when the data had an apostrophe prefix, so I dug further and found the .PrefixCharacter property, which contains an apostrophe when used. Unfortunately this is a read-only property so cannot simply be reset to strip any from a selection of cells :(

[Annalyzer]

I think I can help with this one.

On the menu, select Tools then Options. Click on the Error Checking tab. Under Settings, make sure that Enable background error checking is checked and under Rules make sure that Number stored as text is checked. Close the dialog box.

On the menu, select Edit then GoTo and click the Special... button. Select the Constants radio button, then uncheck Numbers, Logicals, and Errors (leave Text checked). This will highlight all the cells in the worksheet that contain text (don't worry, we're not going to hurt the ones that are supposed to be text).

Error checking will now indicate the errors with an exclamation point. Click on the exclamation point (if there are several, just click one of them) and click Convert to Number.

This will convert all the cells with the apostrophe to numbers, but will not affect the cells which actually have text.

[Stewart Ross]

Well done, Annalyser - I wouldn't have dreamt of using the error checking to find such values, and doing so reveals a few useful features that for me were so well-hidden that I have never used them (Goto, Special for one...). Great stuff!

-Stewart

[NeoPa]

Sounds good Analyser.

Do you have a version number for Excel though, as I can't find this in Excel 2000 :(

[Annalyzer]

I'm using Excel 2003. I didn't know about it when I had Excel 2000, so I couldn't say for sure if it was available or not.

[NeoPa]

That's fine. I'll check it out on 2003 at home.

Thanks :)

[Annalyzer]

Glad I could help. I really love Excel. If there is ever an Excel forum added to this site, you can bet I'll be found hanging out there often. =)

[NeoPa]

That's good to hear :) I quite like Excel myself in fact.

In the mean time, Office VBA is covered in here where necessary and an odd Excel question finds itself in here from time to time. We just don't guarantee to answer it as most of our experts specialise in Access.