replace ' and what it means

[jonasa]

I have a sheet with heaps of cells with the content
'3
'5
'2
...

The cells are displayed with a small green triangle in the upper left
corner.

First: What does this format mean and what is it used for?

Second: To search and replace using ~' does not work for this
particular case.

How can I get rid of all the ' in the cells and replace them with an
empty space to make the cells in to numbers?

There are heaps of referlas to ~ for search of non character strings,
e.g.
http://www.excelforum.com/showthread.php?t=512793&highlight=replace
but I can not find anything about this.

 

[Bob Phillips]

It is used to force numeric data into text format, often happens when
imported from other apps.

To clear it, in the VBIDE run this bit of code

Selection.Value = Selection.Value

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

if you have the contents in column a, then b1=value(a1) and copy down.

“jonasaâ€ç¼–写：

 

[jonasa]

Thanks for the replys.
Sorry - I was not clear enough.
The content in the cells are sometimes of the form

'3 some text
'5 Lisa
'4
'2 Sam

That is - sometimes in is not just a number with a ' in front. I still
want to get rid of the '. Is it possible?

Bob - I dont no enought about the VBIDE, I tried to just write your
code sample in a macrobox, but it seems more code is needed.

wdjsxj - Your code works as a charm except if there is some text in
the cell aswell

 

[Bob Phillips]

Select all the cells in question

Goto the VBIDE, Alt-F11

Get the immediate window up, Ctrl-G

Type the code in the immediate window and hit Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[jonasa]

Thanks Bob,
Its funny how easy it is how to do stuff when you know how. ;-)

I made my own workaround to, allthough your way is more elegant.

Make a new column.
Write
=IF(ISERR(VALUE(a2));a2;VALUE(a2))
in a cell. Copy and paste to fill the column.

This handles the error given by value(a2) for cells with text in.

 

[Pete_UK]

Another way:

Highlight the column with these values in. Click Data |
Text-to-Columns, then click Finish.

Hope this helps.

Pete