Removing characters

[Guest]

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 are:
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.

 

[Carim]

Hi,

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

HTH
Carim

 

[Guest]

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:

 

[Carim]

Hi Stefi,

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

Regards
Carim

 

[Guest]

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

 

[Guest]

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:

 

[Jasoni]

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

 

[Lori]

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