Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract each item in the list
Hi.
The cell A1 contains: Here's the shopping list: pig, dog, hamburger, chocolate..., beef. Please purchase all of them. Thanks! I would like to extract each item separately to different cells, ie B1: =Display1stItem B2: =Display2ndItem .... B?: =DisplayLastItem + CropText ". Please purchase all of them. Thanks!" Reminder: There are different contents with different item list. Plus the wording of the contents and items are subject to change. So something like: MID(A1, 24, 5) is not preferred. Probably they can figure out which word to extract by pattern. In my case, when the list starts, it must start with colon (:). For each item, comma (,) is used to separate each of them. So it would be if a function manages to work like the following: .... ...: pig, cow, button. ... .... Read (:). the extracting starts -- Read pig -- Read (,) -- the item is extracted. Read cow -- Read (,) -- the item is extracted. Read button -- Read (.) -- the item is extracted, and stop reading after that fullstop(.) How to do? -- Additional information: - I'm using Office XP - I'm using Windows XP |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract each item in the list
Hi
Be sure you have X empty columns next to column A, where X is max number of items in single cell. Select the range with entries in column A. From Data menu, select TextToColumns, set delimiter to comma, and finish. You get like this in: column A - "Here's the shopping list: pig" column B - "dog" .... column ? - "beef. Please purchase all of them. Thanks!" When you want to get rid of start/end phrases too, then select any single cell in datarange, snd then select from Edit menu: Replace "Here's the shopping list: " with "" - Replace All Replace ". Please purchase all of them. Thanks!" with "" - Replace All As you can see - no code needed at all, and you need a minute or two for this only. Arvi Laanemets "0-0 Wai Wai ^-^" wrote in message ... Hi. The cell A1 contains: Here's the shopping list: pig, dog, hamburger, chocolate..., beef. Please purchase all of them. Thanks! I would like to extract each item separately to different cells, ie B1: =Display1stItem B2: =Display2ndItem ... B?: =DisplayLastItem + CropText ". Please purchase all of them. Thanks!" Reminder: There are different contents with different item list. Plus the wording of the contents and items are subject to change. So something like: MID(A1, 24, 5) is not preferred. Probably they can figure out which word to extract by pattern. In my case, when the list starts, it must start with colon (:). For each item, comma (,) is used to separate each of them. So it would be if a function manages to work like the following: ... ...: pig, cow, button. ... ... Read (:). the extracting starts -- Read pig -- Read (,) -- the item is extracted. Read cow -- Read (,) -- the item is extracted. Read button -- Read (.) -- the item is extracted, and stop reading after that fullstop(.) How to do? -- Additional information: - I'm using Office XP - I'm using Windows XP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying list item (a bit less vague) | Excel Discussion (Misc queries) | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
cell reference to last item in a list | Excel Discussion (Misc queries) | |||
Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions |