On Mon, 21 Nov 2005 16:20:02 -0800, Loz wrote:

Hi!!

I'm trying to break up a string into sub sections. Eg 1 cat, 2 dogs, 66
chickens...
So far I have managed to get the first number out, but to get the dog count
and the chicken count, I am finding it much more difficult - I think I'm
trying to complicate it the way I'm achieving my results. Can anyone please
help me?

eg.

cats Dogs1 Dogs2 Chicken
1 cat, 3 dogs, 60 chickens 1 1 cat, 3 3 ?
2 cats, 22 dogs, 12 chickens 2 2 cats, 22 22 ?
3 cats, 2 dogs, 8 chickens 3 3 cats, 2 2 ?
4 cats, 21 dogs, 0 chickens 4 4 cats, 21 21 ?
5 cats, 256 dogs 5 5 cats, 256 256 ?

Cats formula: =LEFT(A3,FIND(" ca",A3)-1)
Dogs formula - Dogs1: =LEFT(A3,FIND(" do",A3)-1)
Dogs2: =RIGHT(C3,LEN(C3)-FIND(", ",C3)-1)
Chicken formula: ?

Is it possible to do one formula for each column? instead of breaking it up
like I have it?

TIA
Lauren
(sorry if my columns don't come out in columns...)

Looks like a good application for "regular expressions".

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. I am assuming your strings are in A2:An and your column labels are in B1:x1

3. Make sure the column labels don't conflict with the strings. In
particular, in the string you have "cat" and "cats"; it would be simpler if the
column label was just "Cat". Also, you have column labels "Dogs1" and "Dogs2"
but in the strings you only have "dogs".

4. If you could set up a column naming convention such that the first three
letters of each animal were unique, that would be OK, too. It would allow to
use, for example, "Cats" instead of "Cat" so might look a bit better. (I still
don't understand about the "Dogs1" and Dogs2" though.

5. If your column labels are unique (as in 3), then in B2 use the formula:

=REGEX.MID($A2,"\d+(?=\s+" & B$1 &")",,FALSE)

Then copy/drag this down as far as necessary (B6 in your example). Then select
the column of the formulas and copy/drag it across as far as needed. The cell
references will change automatically.

6. If your column labels are such that only the first three characters are
unique (e.g. Cats), then use this formula instead:

=REGEX.MID($A2,"\d+(?=\s+" & LEFT(B$1,3) &")",,FALSE)

The regular expressions in the formulas look at the string A2 and try to find a
matching pattern that looks like:
One or more digits
followed by one or more spaces
followed by the word that is in the cell at the top of the column
Then return only the digits.

The digits will be returned as text. If you require them to be numeric, merely
precede the formula with a double unary: =--REGEX.MID(...


--ron