Hello,

I have needed advice from you or a formula for removing the some characters
from the cell or columns,
For example
If I have column formatted like text or numbers, and I would like to remove
first one or first two numbers (characters) on the beginning of the field if
they are €œ0€�,

Examle:
Tel. number
03265598
0059842367
05478896
45623178956
00246645898
12355698

The results that I would like to have a
Tel. number
3265598
59842367
5478896
45623178956
246645898
12355698
The same column, but only without €œ0€�
· The column is formatted like text.

I hope that you are going to help me, giving me advice how can I do this or
maybe the problem can be solved by formula.

Thank you.

Hi,

=substitute(A1,"0","")

HTH
Carim

Hi Carim,

I'm afraid your formula won't work when the phone No contains a 0 inside it,
e.g. it converts 03265590 to 326559. I suggest

=TEXT(VALUE(A1),"@")

Regards,
Stefi

€žCarim€� ezt Ã*rta:

Hi,

=substitute(A1,"0","")

HTH
Carim

Hi Stefi,

You are absolutely right ...
It is far better to anticipate problems ...

Regards
Carim

I am not clear why you have to use the TEXT function, doesn't =VALUE(A1) do
the same thing?

"Stefi" wrote:

Hi Carim,

I'm afraid your formula won't work when the phone No contains a 0 inside it,
e.g. it converts 03265590 to 326559. I suggest

=TEXT(VALUE(A1),"@")

Regards,
Stefi

€žCarim€� ezt Ã*rta:

Hi,

=substitute(A1,"0","")

HTH
Carim

Just because Maksko said in his post that his phone Nos are (or may be)
formatted like text. Text function also creates text.

Regards,
Stefi

€žhot dogs€� ezt Ã*rta:

I am not clear why you have to use the TEXT function, doesn't =VALUE(A1) do
the same thing?

"Stefi" wrote:

Hi Carim,

I'm afraid your formula won't work when the phone No contains a 0 inside it,
e.g. it converts 03265590 to 326559. I suggest

=TEXT(VALUE(A1),"@")

Regards,
Stefi

€žCarim€� ezt Ã*rta:

Hi,

=substitute(A1,"0","")

HTH
Carim

"Maksko" kirjoitti
...
Hello,

I have needed advice from you or a formula for removing the some
characters
from the cell or columns,
For example
If I have column formatted like text or numbers, and I would like to
remove
first one or first two numbers (characters) on the beginning of the field
if
they are "0",

Examle:
Tel. number
03265598
0059842367
05478896
45623178956
00246645898
12355698

The results that I would like to have a
Tel. number
3265598
59842367
5478896
45623178956
246645898
12355698
The same column, but only without "0"
· The column is formatted like text.

I hope that you are going to help me, giving me advice how can I do this
or
maybe the problem can be solved by formula.

Thank you.


Lets assume you have aa unformatted information on A column and want the
formatted information to be in B column.
So B1 would be then something like this:

=if(left(A1;2)="00";right(A1;len(A1)-2);if(left(A1;1)="0";right(A1;len(A1)-1);A1))

Jason

Select column containing data and choose data text to columns twice.
-First time just click finish (assuming tab delimited)
-Second time click Next Next and select Text to convert back to text