Breaking up a string into separate columns

[Loz]

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...)

 

[Alok]

Try this in cell B1 when the string is in cell A1
=IF(ISERROR(RIGHT(LEFT($A1,FIND(CHOOSE(COLUMN()-1,"ca","do","ch"),$A1)-1),3)),0,RIGHT(LEFT($A1,FIND(CHOOSE(COLUMN()-1,"ca","do","ch"),$A1)-1),3))

You can copy this formula to C1 and D1. The formula returns number of cats
in column B, number of dogs in column C and chicken in D.

Alok

 

[bpeltzer]

Have you tried using Data > Text To Columns, indicating that the data is
delimited by commas? If that's not viable, the trick will be to use the
third argument to the FIND function, indicating where to start searching for
a comma; you'll want to start looking one character AFTER the first comma is
found.

 

[Loz]

THANK YOU SO MUCH!!!

 

[Loz]

Hmm, sorry. It's kinda working.

=IF(ISERROR(RIGHT(LEFT($A2,FIND(CHOOSE(COLUMN()-1,"ca","do","ch"),$A2)-1),3)),0,RIGHT(LEFT($A2,FIND(CHOOSE(COLUMN()-1,"ca","do","ch"),$A2)-1),3))

In the first column of the formula (cell B2), if there is a 3+ digit figure
for cats, it's not showing the first character. Similar in C2, if there is a
single digit, it shows a space in the front of the number, and doesn't show
the first digit of any 3+ digit numbers.

Any suggestions or alternatives?

Meanwhile, I'll try and nut this formula out!

Thank you!
Lauren

 

[Ron Rosenfeld]

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