Converting data in one cell to 3 different cells

I'm copying nfl.com's schedule to an Excel file.
It places , e.g.,
Miami at Pittsburgh in column A
8:30 PM in column B
What I'd like to have is:
Miami in column A
at in column B
Pittsburgh in column C
( I don't need a time column)
How can that be done ?
The 'at' would be the only constant, but the # of characters on either side
of the at will always be different (San Francisco, Denver, New York Jets, etc)

Thanks,

Check out Data > TextToColumns > Delimited >, using a space as the
delimiter.....

Practice with a copy of your file first...

Vaya con Dios,
Chuck, CABGx3



"Steve" wrote:

> I'm copying nfl.com's schedule to an Excel file.
> It places , e.g.,
> Miami at Pittsburgh in column A
> 8:30 PM in column B
> What I'd like to have is:
> Miami in column A
> at in column B
> Pittsburgh in column C
> ( I don't need a time column)
> How can that be done ?
> The 'at' would be the only constant, but the # of characters on either side
> of the at will always be different (San Francisco, Denver, New York Jets, etc)
>
> Thanks,
>
>

Thanks much. So simple.

Steve

"CLR" wrote:

> Check out Data > TextToColumns > Delimited >, using a space as the
> delimiter.....
>
> Practice with a copy of your file first...
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Steve" wrote:
>
> > I'm copying nfl.com's schedule to an Excel file.
> > It places , e.g.,
> > Miami at Pittsburgh in column A
> > 8:30 PM in column B
> > What I'd like to have is:
> > Miami in column A
> > at in column B
> > Pittsburgh in column C
> > ( I don't need a time column)
> > How can that be done ?
> > The 'at' would be the only constant, but the # of characters on either side
> > of the at will always be different (San Francisco, Denver, New York Jets, etc)
> >
> > Thanks,
> >
> >

You're welcome, happy to be of help

Vaya con Dios,
Chuck, CABGx3



"Steve" wrote:

> Thanks much. So simple.
>
> Steve
>
> "CLR" wrote:
>
> > Check out Data > TextToColumns > Delimited >, using a space as the
> > delimiter.....
> >
> > Practice with a copy of your file first...
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Steve" wrote:
> >
> > > I'm copying nfl.com's schedule to an Excel file.
> > > It places , e.g.,
> > > Miami at Pittsburgh in column A
> > > 8:30 PM in column B
> > > What I'd like to have is:
> > > Miami in column A
> > > at in column B
> > > Pittsburgh in column C
> > > ( I don't need a time column)
> > > How can that be done ?
> > > The 'at' would be the only constant, but the # of characters on either side
> > > of the at will always be different (San Francisco, Denver, New York Jets, etc)
> > >
> > > Thanks,
> > >
> > >

One small glitch. With the duplicates, such as NY Jets/NY Giants & all the
San's ( Francisco, Diego) because of the space's, it's properly putting the
first words in the column, but putting the words after , e.g., the San in
another column.
A B C D
Oak at San Francisco
Is there an easy way to get the D column text into the C column after San ?

Thanks again,


"CLR" wrote:

> You're welcome, happy to be of help
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Steve" wrote:
>
> > Thanks much. So simple.
> >
> > Steve
> >
> > "CLR" wrote:
> >
> > > Check out Data > TextToColumns > Delimited >, using a space as the
> > > delimiter.....
> > >
> > > Practice with a copy of your file first...
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > > "Steve" wrote:
> > >
> > > > I'm copying nfl.com's schedule to an Excel file.
> > > > It places , e.g.,
> > > > Miami at Pittsburgh in column A
> > > > 8:30 PM in column B
> > > > What I'd like to have is:
> > > > Miami in column A
> > > > at in column B
> > > > Pittsburgh in column C
> > > > ( I don't need a time column)
> > > > How can that be done ?
> > > > The 'at' would be the only constant, but the # of characters on either side
> > > > of the at will always be different (San Francisco, Denver, New York Jets, etc)
> > > >
> > > > Thanks,
> > > >
> > > >

Using column E as a helper, put this formula and copy down.....

=IF(COUNTA(C1:D1)=2,C1&" "&D1,C1)

Then, highlight column E and Copy > PasteSpecial > Values, to get rid of the
formulas, then replace column C with it if you wish and delete column D

hth
Vaya con Dios,
Chuck, CABGx3



"Steve" wrote:

> One small glitch. With the duplicates, such as NY Jets/NY Giants & all the
> San's ( Francisco, Diego) because of the space's, it's properly putting the
> first words in the column, but putting the words after , e.g., the San in
> another column.
> A B C D
> Oak at San Francisco
> Is there an easy way to get the D column text into the C column after San ?
>
> Thanks again,
>
>
> "CLR" wrote:
>
> > You're welcome, happy to be of help
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Steve" wrote:
> >
> > > Thanks much. So simple.
> > >
> > > Steve
> > >
> > > "CLR" wrote:
> > >
> > > > Check out Data > TextToColumns > Delimited >, using a space as the
> > > > delimiter.....
> > > >
> > > > Practice with a copy of your file first...
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > >
> > > > "Steve" wrote:
> > > >
> > > > > I'm copying nfl.com's schedule to an Excel file.
> > > > > It places , e.g.,
> > > > > Miami at Pittsburgh in column A
> > > > > 8:30 PM in column B
> > > > > What I'd like to have is:
> > > > > Miami in column A
> > > > > at in column B
> > > > > Pittsburgh in column C
> > > > > ( I don't need a time column)
> > > > > How can that be done ?
> > > > > The 'at' would be the only constant, but the # of characters on either side
> > > > > of the at will always be different (San Francisco, Denver, New York Jets, etc)
> > > > >
> > > > > Thanks,
> > > > >
> > > > >

Another way is to use two formulas, assuming your data in column A,
Put this in B1 and copy down
=LEFT(A1,FIND(" at ",A1,1)-1)
Put this in C1 and copy down
=MID(A1,FIND(" at ",A1,1)+4,99)

Vaya con Dios,
Chuck, CABGx3





"Steve" wrote:

> I'm copying nfl.com's schedule to an Excel file.
> It places , e.g.,
> Miami at Pittsburgh in column A
> 8:30 PM in column B
> What I'd like to have is:
> Miami in column A
> at in column B
> Pittsburgh in column C
> ( I don't need a time column)
> How can that be done ?
> The 'at' would be the only constant, but the # of characters on either side
> of the at will always be different (San Francisco, Denver, New York Jets, etc)
>
> Thanks,
>
>