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