Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
0-0 Wai Wai ^-^
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying list item (a bit less vague) Hru48 Excel Discussion (Misc queries) 7 September 29th 05 04:51 PM
Extract multiple records matching criteria from list William DeLeo Excel Worksheet Functions 12 June 30th 05 02:35 PM
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM
cell reference to last item in a list Lee Excel Discussion (Misc queries) 1 February 2nd 05 09:07 PM
Selecting an Item from a List and getting a different item to pop. Matt Excel Worksheet Functions 1 December 7th 04 03:37 PM


All times are GMT +1. The time now is 06:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"