Chop off parts of the statement

[0-0 Wai Wai ^-^]

Chop off parts of the statement

Hi.
I would like to do the following:
1) Chop the right part
eg For cells containing "Hi. I'm John"; "Hi, I'm Peter" [without quotes], chop
off the rightmost 8 units, ie "Hi. I'm " [without quotes]

2) Chop the middle part
eg chop any unit between 5-12 (inclusive). For statement like "It's a sunny
day." , it will become "It's day" [without quotes],

3) Chop the left part
eg For cells containing 123 456 000; 345 455 000, chop off the leftmost 3
units, ie the last 3 figures

4) Chop based on criteria
eg For cells containing "There're lots of things like apples, bananas, pears
etc.", chop off anything from the right until it meets the word "like" OR chop
off anything from the left untile it meets the word "apples".

Thank you very much.

 

[Niek Otten]

1)
You probably mean "leave the leftmost 8"
=LEFT(A1,8)
2)
=LEFT(A2,4)&RIGHT(A2,LEN(A2)-12)
3)
=RIGHT(A3,LEN(A3)-3)
4)
=LEFT(A4,FIND("@",SUBSTITUTE(A4,"like","like@")))

--
Kind regards,

Niek Otten

|
| Chop off parts of the statement
|
| Hi.
| I would like to do the following:
| 1) Chop the right part
| eg For cells containing "Hi. I'm John"; "Hi, I'm Peter" [without quotes], chop
| off the rightmost 8 units, ie "Hi. I'm " [without quotes]
|
| 2) Chop the middle part
| eg chop any unit between 5-12 (inclusive). For statement like "It's a sunny
| day." , it will become "It's day" [without quotes],
|
| 3) Chop the left part
| eg For cells containing 123 456 000; 345 455 000, chop off the leftmost 3
| units, ie the last 3 figures
|
| 4) Chop based on criteria
| eg For cells containing "There're lots of things like apples, bananas, pears
| etc.", chop off anything from the right until it meets the word "like" OR chop
| off anything from the left untile it meets the word "apples".
|
| Thank you very much.
|
| --
| Additional information:
| - I'm using Office XP
| - I'm using Windows XP
|
|