Removing characters

[Guest]

Hi to all,

Of the beginning I would like to thank you for the advice about my previous
question, it was useful.

I have needed advice for a second time or maybe a formula for removing some
characters from the cell or columns,

For example
If I have column with a tel. numbers and I would like to remove first three
numbers (characters) if they are ``555`` on the beginning of the field.

Example:
Tel. number
5553265598
555984236
5478896
45623178956
78246645
555123556

The results that I would like to have are:
Tel. number
3265598
984236
5478896
45623178956
78246645
123556
The same column, but only without “555â€

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.

 

[Guest]

=IF(LEFT(A2,3)="555",MID(A2,4,254),A2)

Regards,
Stefi

„Maksko†ezt írta:

 

[Guest]

Hi Stefi,
Thank you for Your advice, but i tried now and its not working, can You give
me same explanation about the formula.

Onceagain thank You for your support.

Best Regards, maksko

 

[Guest]

If your tel. numbers are in column A then use a helper column, say B, enter
the formula in B2 and drag it down as needed. In column B you'll have the
truncated numbers!

Regards,
Stefi

„Maksko†ezt írta:

 

[Guest]

If you want to place the truncated numbers back to column A then select
column B, Copy/PasteSpecial-values to column A. Then you can delete the
helper column!

Stefi


„Maksko†ezt írta:

 

[Guest]

Hi, Stefi

Thank You for the formula, I tried and its working, but now I have a another
problem, The formula working only if the number is 555, can You help me and
let me know how to set the formula if the numbers are 245, 356, 355, 381,
387, 389 or maybe some others.

Thank You, You are helping me a lot.

Best regards, Maksko

 

[Guest]

=IF(OR(LEFT(A2,3)="555",LEFT(A2,3)="245",LEFT(A2,3)="356"),MID(A2,4,254),A2)
You can enhance the OR() function with up to 30 additional conditions as
required:
OR(LEFT(A2,3)="555",LEFT(A2,3)="245",LEFT(A2,3)="356"; ... )

Regards,
Stefi

„Maksko†ezt írta:

 

[Guest]

Hi Stefi

Thank you, now its ok.

Thank you for your support.

Best regards
Maksko

 

[Guest]

You are welcome! Thanks for the feedback!
Stefi

Hi Stefi

Thank you, now its ok.

Thank you for your support.

Best regards
Maksko