Put parts of cell into separate columns

[gary]

For each cell in Col A, how do I put:

the beginning characters [up to the first space (" ")] into Col B
the last 4 characters into Col C
the other characters into Col D.

Here is a sample of my spreadsheet:

A
3ABN Three Angels Broadcasting Network 9393
ABCF ABC Family 180
AHC American Heroes Channel 195
ALIVE America Live 219

The result should be:

B C D
3ABN Three Angels Broadcasting Network 9393
ABCF ABC Family 180
AHC American Heroes Channel 195
ALIVE America Live 219

 

[Claus Busch]

Hi Gary,

Am Fri, 25 Apr 2014 12:27:16 -0700 (PDT) schrieb gary:

A
3ABN Three Angels Broadcasting Network 9393
ABCF ABC Family 180
AHC American Heroes Channel 195
ALIVE America Live 219

The result should be:

B C D
3ABN Three Angels Broadcasting Network 9393
ABCF ABC Family 180
AHC American Heroes Channel 195
ALIVE America Live 219

you have trailing spaces in column A. If you first delete the trailing
space with Text to Columns you can use
in B1: =LEFT(A1,FIND(" ",A1)-1)
in D1: =TRIM(RIGHT(A1,4))
in C1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,),D1,))

If you don't want to delete the trailing space change the formula in D1
to: =TRIM(RIGHT(TRIM(A1),4))


Regards
Claus B.

 

[gary]

I've changed "the ending characters..." line:

For each cell in Col A, how do I put:

the beginning characters [up to the first space (" ")] into Col B
the ending characters [after the last space (" ")] into Col C
the other characters into Col D.

(I do want to delete the trailing spaces).

 

[Claus Busch]

Hi Gary,

Am Fri, 25 Apr 2014 14:34:59 -0700 (PDT) schrieb gary:

the beginning characters [up to the first space (" ")] into Col B
the ending characters [after the last space (" ")] into Col C
the other characters into Col D.

no need to start a new thread in another group.
Answered in excel.misc


Regards
Claus B.