| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing characters
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 a 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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing characters
=IF(LEFT(A2,3)="555",MID(A2,4,254),A2)
Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 a 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing characters
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 "Stefi" wrote: =IF(LEFT(A2,3)="555",MID(A2,4,254),A2) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 a 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing characters
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: 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 "Stefi" wrote: =IF(LEFT(A2,3)="555",MID(A2,4,254),A2) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 a 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing characters
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: 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 "Stefi" wrote: =IF(LEFT(A2,3)="555",MID(A2,4,254),A2) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 a 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing characters
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 "Stefi" wrote: 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: 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 "Stefi" wrote: =IF(LEFT(A2,3)="555",MID(A2,4,254),A2) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 a 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing characters
=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)="3 56"; ... ) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 "Stefi" wrote: 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: 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 "Stefi" wrote: =IF(LEFT(A2,3)="555",MID(A2,4,254),A2) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 a 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing characters
Hi Stefi
Thank you, now its ok. Thank you for your support. Best regards Maksko "Stefi" wrote: =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)="3 56"; ... ) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 "Stefi" wrote: 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: 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 "Stefi" wrote: =IF(LEFT(A2,3)="555",MID(A2,4,254),A2) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 a 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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing characters
You are welcome! Thanks for the feedback!
Stefi "Maksko" wrote: Hi Stefi Thank you, now its ok. Thank you for your support. Best regards Maksko "Stefi" wrote: =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)="3 56"; ... ) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 "Stefi" wrote: 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: 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 "Stefi" wrote: =IF(LEFT(A2,3)="555",MID(A2,4,254),A2) Regards, Stefi €žMaksko€ť ezt Ă*rta: 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 a 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing characters | Excel Discussion (Misc queries) | |||
| Removing characters from a cell (keeping only the numbers) | Excel Discussion (Misc queries) | |||
| Removing characters from a cell (keeping only numbers) 2 | Excel Discussion (Misc queries) | |||
| Removing unwanted characters | Excel Discussion (Misc queries) | |||
| removing pre-set characters from comments | Excel Worksheet Functions | |||
Linear Mode
