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,

Converting data in one cell to 3 different cells
 

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,

Converting data in one cell to 3 different cells
 

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,

Converting data in one cell to 3 different cells
 

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,

Converting data in one cell to 3 different cells
 

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,

Converting data in one cell to 3 different cells
 

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,

Converting data in one cell to 3 different cells
 

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,