Put parts of cell into separate columns

  • Thread starter Thread starter gary
  • Start date Start date
G

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
 
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
Click to expand...

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.
 
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).
 
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.
Click to expand...

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


Regards
Claus B.
 
Back
Top