Seperate data in a cell

  • Thread starter Thread starter Ian P
  • Start date Start date
I

Ian P

Hi,

I am trying to seperate information in a cell.

e.g.

in a single cell I have the name John Smith
I want to seperate that into two cells and have John in
one, and Smith in the other. I have around 1000 of these,
so I don't really want to do it manually.

any ideas?

Thanks
Ian
 
Ian

Try using Data/Text to Columns.
There are other ways, but it's a good place to start.

Andy.
 
Didn't really understand the text to column thing, however
I have found a solution. I copied the info and pasted
into a .txt file I then imported it back into Excel using
space as the delimited character. it worked a treat.

Thanks
Ian
 
A method to perform the split using Excel functions as
opposed to the multiple step process you used... look at
this formula to split the data:
Assuming the text string of "John Smith" exists in cell A1

=MID(A1,1,FIND(" ",A1,1))

MID returns a specific number of characters from a text
string, starting at the position you specify, based on the
number of characters you specify. This formula will look
in cell A1 beginning in position #1 until it FINDS
a " "(space) in the original string and will
produce "John" in the new cell... likewise adjust the
formula to look at cell A1 to FIND a " "(space) add 1 and
use this as the starting point for the second string
extract as in the following formula:
=MID(A1,FIND(" ",A1,1)+1,99)
 
fantastic! I will use this for the other worksheets I
have to do.

Thanks for your help
Ian
 
Back
Top