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...hlight=replace
but I can not find anything about this.


--
jonasa
------------------------------------------------------------------------
jonasa's Profile: http://www.excelforum.com/member.php...o&userid=31722
View this thread: http://www.excelforum.com/showthread...hreadid=514316

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)

"jonasa" wrote in
message ...

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...hlight=replace
but I can not find anything about this.


--
jonasa
------------------------------------------------------------------------
jonasa's Profile:
http://www.excelforum.com/member.php...o&userid=31722
View this thread: http://www.excelforum.com/showthread...hreadid=514316

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

€œ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...hlight=replace
but I can not find anything about this.


--
jonasa
------------------------------------------------------------------------
jonasa's Profile: http://www.excelforum.com/member.php...o&userid=31722
View this thread: http://www.excelforum.com/showthread...hreadid=514316

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 :-)


--
jonasa
------------------------------------------------------------------------
jonasa's Profile: http://www.excelforum.com/member.php...o&userid=31722
View this thread: http://www.excelforum.com/showthread...hreadid=514316

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" wrote in
message ...

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 :-)


--
jonasa
------------------------------------------------------------------------
jonasa's Profile:
http://www.excelforum.com/member.php...o&userid=31722
View this thread: http://www.excelforum.com/showthread...hreadid=514316

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.


--
jonasa
------------------------------------------------------------------------
jonasa's Profile: http://www.excelforum.com/member.php...o&userid=31722
View this thread: http://www.excelforum.com/showthread...hreadid=514316

Another way:

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

Hope this helps.

Pete