Remove the caracter ' from cells in excel 1

[hefly]

I have imported an excel spreadsheet. Every cell has an appostrophe leading the text or number in the cell.

I need to remove that appostrophe (')

How do I remove this character?

Thanks.

Hefly

 

[AnotherHiggins]

First - why do you need to remove the apostrophe?

I ask because the only thing that does in Excel is tell it to treat what follows as text. If you remove them, and you have numeric entries, they will be switched from TEXT to actual NUMBERS.

This is undesirable if, for instance, you have leading zeros that you want to keep.

The apostrophes do not affect alphanumeric data one bit.

If, on the other hand, you really want to convert numeric data from text to numbers, then there are several methods you can employ.

A popular method is the "Times One Fix".
[ul][li]In a cell to the right of all the imported data, type [blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the cells that are giving you trouble^[red]*[/red][/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now try applying a new format to those cells [/li][/ul]

^[red]*[/red] Note that any null cells that you select will be converted to zeros. For this reason I recommend that you don’t select an entire row/column.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[hefly]

I am importing into Access. I think there was some problem with the leading apostrophe in access.

Thanks.

Hefly

 

[AnotherHiggins]

-> I think there was some problem with the leading apostrophe in access.

Details are helpful.

Did you try the above fix in Excel, before importing into Access?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[BlueHorizon]

Hi,
In Word 2003:
If the entries are values: Select the cells and click on the arrow next to the error checking pop-up. Select Convert to number.
If the entries are text: Select the cells and copy. Select the cells and right-click/Paste Special/Values.

Hope this helps!

Best,
Blue Horizon

 

[hefly]

I see there are several ways to skin that cat. I find the way to remove the apostrophe before a number is to choose the apostrophe as the delimiter then in the data tab, text to columns. The whole column is converted.

Hefly.

 

[kjv1611]

--> I think there was some problem with the leading apostrophe in access.

-->Details are helpful.

[ROFL]

--

"If to err is human, then I must be some kind of human!" -Me

 

[hefly]

Sorry guys. I couldn't find any issues other than the numbers appearing as text. It must be those pesky carriage returns that I was thinking about.

Hefly

 

[hefly]

Thanks again for your replies.

Finally, I found a solution to getting rid of leading apostrophes AND realized the problem with apostrophe. It renders whatever in the cell as text. If that happens to be a formula, then you see the formlula and not the calculation. So, it's not just numbers that are affected.

I found an article discussing the problem and a macro publised at vbaexpress that will remove all leading apostrophes in an excel spreadsheet.

Thank you again for your helpful comments.

Hefly

 

[AnotherHiggins]

FYI: the second sentence in my first post:

...the only thing [the apostrophe] does in Excel is tell it to treat what follows as text.

I don't mean to be a jerk, but if your question had explained your problem in detail, this probably would have been answered on day one.

Glad you got it sorted, though. And thanks for posting back to let us know you found a solution.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

FYI,

You may not have solved your problem by simply removing the leading apostrophy from numbers.

IF your column contains ONLY numbers and does NOT ALSO contain text, then you are OK.

However, if your column contains BOTH NUMBERS & TEXT, you will get INCOMPLETE data (depending on what is in the first 8 rows of data). In this case, you are much better off leaving the leading apostrophy, since your column probably contains IDENTIFIERS that you will never perform math on anyhow.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}