replace ' and what it means
 

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

replace ' and what it means
 

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

replace ' and what it means
 

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

replace ' and what it means
 

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

replace ' and what it means
 

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

replace ' and what it means
 

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

replace ' and what it means
 

Another way:

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

Hope this helps.

Pete