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.

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" <jonasa.23irka_1140424501.271@excelforum-nospam.com> wrote in
message news:jonasa.23irka_1140424501.271@excelforum-nospam.com...
>
> 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 :-)

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" <jonasa.23iv1a_1140429001.2753@excelforum-nospam.com> wrote in
message news:jonasa.23iv1a_1140429001.2753@excelforum-nospam.com...
>
> 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.

Another way:

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

Hope this helps.

Pete