Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking up a string into separate columns
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...) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking up a string into separate columns
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 "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...) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking up a string into separate columns
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" 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...) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking up a string into separate columns
THANK YOU SO MUCH!!!
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking up a string into separate columns
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking up a string into separate columns
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
inserting columns within certain rows only | Excel Discussion (Misc queries) | |||
Hidden Columns in Shared Workbooks | Excel Discussion (Misc queries) | |||
Get Msg "Cannot shift objects off sheet" when hidng columns" Why? | Excel Discussion (Misc queries) | |||
split a single column into 2 separate columns | Excel Worksheet Functions |