Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Chop off parts of the statement
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Chop off parts of the statement
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 "0-0 Wai Wai ^-^" wrote in message ... | | 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 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SET statement tutorial | Excel Discussion (Misc queries) | |||
If statement | Excel Discussion (Misc queries) | |||
Not sure which function and how to use it. Help | Excel Worksheet Functions | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
Matching parts to model numbers | Excel Discussion (Misc queries) |