|
Extract Portion of field into other fields
I have this field containing data like such in column (B15:B21):
12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 12 9 50 2 12 9 50 1 12 9 49 4 What I need to do is pull out the various components into three other column fields (C),(D),(E). I need to have the 12 9 to be in column (C) and be the month 12 day 9 , then in column (D) have the value 1, 50 and 49 and in column (E) have the value 2, 1, 4,3,2,1,4. Basically a parsing function using the spaces between the values to seperate them into respective cells. Is this possible, I know it is in Access but I'm at a loss for Excel. Looking forward to your input.... Thanks |
Hi
try starting with 'Data - Text to columns' for this -- Regards Frank Kabel Frankfurt, Germany "Jay" schrieb im Newsbeitrag ... I have this field containing data like such in column (B15:B21): 12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 12 9 50 2 12 9 50 1 12 9 49 4 What I need to do is pull out the various components into three other column fields (C),(D),(E). I need to have the 12 9 to be in column (C) and be the month 12 day 9 , then in column (D) have the value 1, 50 and 49 and in column (E) have the value 2, 1, 4,3,2,1,4. Basically a parsing function using the spaces between the values to seperate them into respective cells. Is this possible, I know it is in Access but I'm at a loss for Excel. Looking forward to your input.... Thanks |
C15:
=LEFT(B15,4) D15: =MID(B15,6,1+LEN(B15)=9) E15: =RIGHT(B15) HTH Jason Atlanta, GA -----Original Message----- I have this field containing data like such in column (B15:B21): 12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 12 9 50 2 12 9 50 1 12 9 49 4 What I need to do is pull out the various components into three other column fields (C),(D),(E). I need to have the 12 9 to be in column (C) and be the month 12 day 9 , then in column (D) have the value 1, 50 and 49 and in column (E) have the value 2, 1, 4,3,2,1,4. Basically a parsing function using the spaces between the values to seperate them into respective cells. Is this possible, I know it is in Access but I'm at a loss for Excel. Looking forward to your input.... Thanks . |
Actually, I used this in D15:
=MID(B16,6,1+(LEN(B16)=9)*1) Jason -----Original Message----- C15: =LEFT(B15,4) D15: =MID(B15,6,1+LEN(B15)=9) E15: =RIGHT(B15) HTH Jason Atlanta, GA -----Original Message----- I have this field containing data like such in column (B15:B21): 12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 12 9 50 2 12 9 50 1 12 9 49 4 What I need to do is pull out the various components into three other column fields (C),(D),(E). I need to have the 12 9 to be in column (C) and be the month 12 day 9 , then in column (D) have the value 1, 50 and 49 and in column (E) have the value 2, 1, 4,3,2,1,4. Basically a parsing function using the spaces between the values to seperate them into respective cells. Is this possible, I know it is in Access but I'm at a loss for Excel. Looking forward to your input.... Thanks . . |
You could try datatext to columns and choose space a delimiter, that will
parse all then you just merge the first 2 columns like if you got 12 in C, 9 in D, 1 i E and 2 in F, then use one extra column and =C15&" "&D15 then copy down, that's what I would do If you want a formula, in C15 put =LEFT(B15,FIND("^^",SUBSTITUTE(B15," ","^^",2))) in D15 put =LEFT(SUBSTITUTE(B15,C15,""),FIND(" ",SUBSTITUTE(B15,C15,""))-1) and in E15 put =MID(B15,FIND("^^",SUBSTITUTE(B15," ","^^",3)),255) Regards, Peo Sjoblom "Jay" wrote: I have this field containing data like such in column (B15:B21): 12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 12 9 50 2 12 9 50 1 12 9 49 4 What I need to do is pull out the various components into three other column fields (C),(D),(E). I need to have the 12 9 to be in column (C) and be the month 12 day 9 , then in column (D) have the value 1, 50 and 49 and in column (E) have the value 2, 1, 4,3,2,1,4. Basically a parsing function using the spaces between the values to seperate them into respective cells. Is this possible, I know it is in Access but I'm at a loss for Excel. Looking forward to your input.... Thanks |
12 9 1 2
12 9 1 1 12 9 50 4 12 9 50 3 Okay guys, I've got this thing working, sort of! I've used this function combination : =LEFT((B5),2)& " /"&MID((B5),3,3) to get this result which is this 12 / 9. Now I need to convert this to a real date of month 12/ day 9. Is this possible? I also have parsed out the two values as 12 and 9 as numeric in other columns F=12 and G=9. Is there a method of creating a date value combining those two columns if the previous is not possible? Thanks, "Peo Sjoblom" wrote: You could try datatext to columns and choose space a delimiter, that will parse all then you just merge the first 2 columns like if you got 12 in C, 9 in D, 1 i E and 2 in F, then use one extra column and =C15&" "&D15 then copy down, that's what I would do If you want a formula, in C15 put =LEFT(B15,FIND("^^",SUBSTITUTE(B15," ","^^",2))) in D15 put =LEFT(SUBSTITUTE(B15,C15,""),FIND(" ",SUBSTITUTE(B15,C15,""))-1) and in E15 put =MID(B15,FIND("^^",SUBSTITUTE(B15," ","^^",3)),255) Regards, Peo Sjoblom "Jay" wrote: I have this field containing data like such in column (B15:B21): 12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 12 9 50 2 12 9 50 1 12 9 49 4 What I need to do is pull out the various components into three other column fields (C),(D),(E). I need to have the 12 9 to be in column (C) and be the month 12 day 9 , then in column (D) have the value 1, 50 and 49 and in column (E) have the value 2, 1, 4,3,2,1,4. Basically a parsing function using the spaces between the values to seperate them into respective cells. Is this possible, I know it is in Access but I'm at a loss for Excel. Looking forward to your input.... Thanks |
I copied and pasted your example into a WS and, if what you posted is
*exactly* what you have, you can do it in one shot using Text To Columns. Select B15:B21, and then: <Data <TextToColumns And click on "FixedWidth", then <Next, Then click on, and drag the break lines to separate the data as you described. The space placement in your example enabled the break lines to separate the numbers as you wish. Then <Next The "Data Preview" window should show that the first column containing the "12 9" is *selected* (colored black). If not, just click in this first column to select it. Under the "Column Data Format" box, click on "Date", and make sure "MDY" is displayed in the date box. The "MDY" is now displayed over the "12 9" selected column, and "General" is above the other two. Now, just click <Finish. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jay" wrote in message ... 12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 Okay guys, I've got this thing working, sort of! I've used this function combination : =LEFT((B5),2)& " /"&MID((B5),3,3) to get this result which is this 12 / 9. Now I need to convert this to a real date of month 12/ day 9. Is this possible? I also have parsed out the two values as 12 and 9 as numeric in other columns F=12 and G=9. Is there a method of creating a date value combining those two columns if the previous is not possible? Thanks, "Peo Sjoblom" wrote: You could try datatext to columns and choose space a delimiter, that will parse all then you just merge the first 2 columns like if you got 12 in C, 9 in D, 1 i E and 2 in F, then use one extra column and =C15&" "&D15 then copy down, that's what I would do If you want a formula, in C15 put =LEFT(B15,FIND("^^",SUBSTITUTE(B15," ","^^",2))) in D15 put =LEFT(SUBSTITUTE(B15,C15,""),FIND(" ",SUBSTITUTE(B15,C15,""))-1) and in E15 put =MID(B15,FIND("^^",SUBSTITUTE(B15," ","^^",3)),255) Regards, Peo Sjoblom "Jay" wrote: I have this field containing data like such in column (B15:B21): 12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 12 9 50 2 12 9 50 1 12 9 49 4 What I need to do is pull out the various components into three other column fields (C),(D),(E). I need to have the 12 9 to be in column (C) and be the month 12 day 9 , then in column (D) have the value 1, 50 and 49 and in column (E) have the value 2, 1, 4,3,2,1,4. Basically a parsing function using the spaces between the values to seperate them into respective cells. Is this possible, I know it is in Access but I'm at a loss for Excel. Looking forward to your input.... Thanks |
I left out one step.
In order to move the separation into Columns C, D, and E, and allow the original data to remain unchanged in Column B, you have to stipulate exactly where you wish the separation to *begin*. You do this by simply changing the address in the €śDestination€ť box from B15 to C15 before you click on <Finish. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "RagDyer" wrote in message ... I copied and pasted your example into a WS and, if what you posted is *exactly* what you have, you can do it in one shot using Text To Columns. Select B15:B21, and then: <Data <TextToColumns And click on "FixedWidth", then <Next, Then click on, and drag the break lines to separate the data as you described. The space placement in your example enabled the break lines to separate the numbers as you wish. Then <Next The "Data Preview" window should show that the first column containing the "12 9" is *selected* (colored black). If not, just click in this first column to select it. Under the "Column Data Format" box, click on "Date", and make sure "MDY" is displayed in the date box. The "MDY" is now displayed over the "12 9" selected column, and "General" is above the other two. Now, just click <Finish. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jay" wrote in message ... 12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 Okay guys, I've got this thing working, sort of! I've used this function combination : =LEFT((B5),2)& " /"&MID((B5),3,3) to get this result which is this 12 / 9. Now I need to convert this to a real date of month 12/ day 9. Is this possible? I also have parsed out the two values as 12 and 9 as numeric in other columns F=12 and G=9. Is there a method of creating a date value combining those two columns if the previous is not possible? Thanks, "Peo Sjoblom" wrote: You could try datatext to columns and choose space a delimiter, that will parse all then you just merge the first 2 columns like if you got 12 in C, 9 in D, 1 i E and 2 in F, then use one extra column and =C15&" "&D15 then copy down, that's what I would do If you want a formula, in C15 put =LEFT(B15,FIND("^^",SUBSTITUTE(B15," ","^^",2))) in D15 put =LEFT(SUBSTITUTE(B15,C15,""),FIND(" ",SUBSTITUTE(B15,C15,""))-1) and in E15 put =MID(B15,FIND("^^",SUBSTITUTE(B15," ","^^",3)),255) Regards, Peo Sjoblom "Jay" wrote: I have this field containing data like such in column (B15:B21): 12 9 1 2 12 9 1 1 12 9 50 4 12 9 50 3 12 9 50 2 12 9 50 1 12 9 49 4 What I need to do is pull out the various components into three other column fields (C),(D),(E). I need to have the 12 9 to be in column (C) and be the month 12 day 9 , then in column (D) have the value 1, 50 and 49 and in column (E) have the value 2, 1, 4,3,2,1,4. Basically a parsing function using the spaces between the values to seperate them into respective cells. Is this possible, I know it is in Access but I'm at a loss for Excel. Looking forward to your input.... Thanks |
| All times are GMT +1. The time now is 07:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com