Splitting a cell, with a twist!

Hi,

I have data (names) which appear with Surname first, with all other names
after that. (all in one cell). For example:

Earles Prof Stanley William Edward

What I want to do, is split this so that the first word (in this case
"Earles" stays, and the rest is shifted into a new column.

Any ideas?

PS...I cant get Data, text to columns to work!

Data, Text to Columns, Tick delimited, <Next> Select "Space" as delimiter,
<Next>, leave format as General, <Finish> and it will split this entry into 5
columns

"christinab" wrote:

> Hi,
>
> I have data (names) which appear with Surname first, with all other names
> after that. (all in one cell). For example:
>
> Earles Prof Stanley William Edward
>
> What I want to do, is split this so that the first word (in this case
> "Earles" stays, and the rest is shifted into a new column.
>
> Any ideas?
>
> PS...I cant get Data, text to columns to work!

Thanks, but not quite what I wanted!

Instead of it going into 5 new columns, I just want it all to go into one
new one, leaving the person's surname behind in the old one.



"Kassie" wrote:

> Data, Text to Columns, Tick delimited, <Next> Select "Space" as delimiter,
> <Next>, leave format as General, <Finish> and it will split this entry into 5
> columns
>
> "christinab" wrote:
>
> > Hi,
> >
> > I have data (names) which appear with Surname first, with all other names
> > after that. (all in one cell). For example:
> >
> > Earles Prof Stanley William Edward
> >
> > What I want to do, is split this so that the first word (in this case
> > "Earles" stays, and the rest is shifted into a new column.
> >
> > Any ideas?
> >
> > PS...I cant get Data, text to columns to work!

Hi,

Use the find formula to tell you where the first space is. And the mid
formula to grab everything else to the right of that

i.e. to get Prof Stanley William Edward try

mid("Earles Prof Stanley William Edward" , Find(" ","Earles Prof Stanley
William Edward")+1,1000)


For Earles try

left( "Earles Prof Stanley William Edward",Find(" ","Earles Prof Stanley
William Edward") - 1)

Good luck

cheers

D

"christinab" wrote:

> Hi,
>
> I have data (names) which appear with Surname first, with all other names
> after that. (all in one cell). For example:
>
> Earles Prof Stanley William Edward
>
> What I want to do, is split this so that the first word (in this case
> "Earles" stays, and the rest is shifted into a new column.
>
> Any ideas?
>
> PS...I cant get Data, text to columns to work!

You cannot self-refer to a cell value, so youre going to have to either write
a macro or generate the two data values in new cells

"christinab" wrote:

> Thanks, but not quite what I wanted!
>
> Instead of it going into 5 new columns, I just want it all to go into one
> new one, leaving the person's surname behind in the old one.
>
>
>
> "Kassie" wrote:
>
> > Data, Text to Columns, Tick delimited, <Next> Select "Space" as delimiter,
> > <Next>, leave format as General, <Finish> and it will split this entry into 5
> > columns
> >
> > "christinab" wrote:
> >
> > > Hi,
> > >
> > > I have data (names) which appear with Surname first, with all other names
> > > after that. (all in one cell). For example:
> > >
> > > Earles Prof Stanley William Edward
> > >
> > > What I want to do, is split this so that the first word (in this case
> > > "Earles" stays, and the rest is shifted into a new column.
> > >
> > > Any ideas?
> > >
> > > PS...I cant get Data, text to columns to work!

If you have a variety of names then the only thing I can think of is
GETELEMENT (an UDF written by some whizzz). The format is
=GETELEMENT(text,n,delimiter).

So, in cell A1 I have Paul Smith - in cell B1 you could write
=GETELEMET(A1,1," ") and it would return "Paul". If you wanted Smith you
would write =GETELEMENT(A1,2," ").

A1 is the cell with the text, 1 or two is which element you want to return
and the " " is a space delimiter.

Create a module in the workbook (Alt+F11, Insert->Module).

Paste this code:

Function GETELEMENT(text As Variant, n As Integer, _
delimiter As String) As String

' Extracts the nth element from a string.

Dim txt, str As String
Dim count, i As Integer

'Manipulate a copy of the text string
txt = text

'If a space is used as the delimiter, remove extra spaces
If delimiter = Chr(32) Then txt = Application.Trim(txt)

'Add a delimiter to the end of the string
If Right(txt, Len(txt)) <> delimiter Then
txt = txt & delimiter
End If

'Initialize count and element
count = 0
str = ""

'Get each element
For i = 1 To Len(txt)
If Mid(txt, i, 1) = delimiter Then
count = count + 1
If count = n Then
GETELEMENT = str
Exit Function
Else
str = ""
End If
Else
str = str & Mid(txt, i, 1)
End If
Next i
GETELEMENT = ""

End Function

"DazzaData" wrote:

> Hi,
>
> Use the find formula to tell you where the first space is. And the mid
> formula to grab everything else to the right of that
>
> i.e. to get Prof Stanley William Edward try
>
> mid("Earles Prof Stanley William Edward" , Find(" ","Earles Prof Stanley
> William Edward")+1,1000)
>
>
> For Earles try
>
> left( "Earles Prof Stanley William Edward",Find(" ","Earles Prof Stanley
> William Edward") - 1)
>
> Good luck
>
> cheers
>
> D
>
> "christinab" wrote:
>
> > Hi,
> >
> > I have data (names) which appear with Surname first, with all other names
> > after that. (all in one cell). For example:
> >
> > Earles Prof Stanley William Edward
> >
> > What I want to do, is split this so that the first word (in this case
> > "Earles" stays, and the rest is shifted into a new column.
> >
> > Any ideas?
> >
> > PS...I cant get Data, text to columns to work!

To pull out the Surname into a cell:

=LEFT(A1,FIND(" ",A1)-1)

The rest of it:

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

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hi,
>
>I have data (names) which appear with Surname first,
with all other names
>after that. (all in one cell). For example:
>
>Earles Prof Stanley William Edward
>
>What I want to do, is split this so that the first word
(in this case
>"Earles" stays, and the rest is shifted into a new
column.
>
>Any ideas?
>
>PS...I cant get Data, text to columns to work!
>.
>

If it is as structured as you say

Surname: = LEFT(A1,FIND(" ",A1)-1)
Rest: =MID(A1,FIND(" ",A1)+1,255)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"christinab" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have data (names) which appear with Surname first, with all other names
> after that. (all in one cell). For example:
>
> Earles Prof Stanley William Edward
>
> What I want to do, is split this so that the first word (in this case
> "Earles" stays, and the rest is shifted into a new column.
>
> Any ideas?
>
> PS...I cant get Data, text to columns to work!

Hey, this worked like a charm!

Thanks everyone for your time!


"Jason Morin" wrote:

> To pull out the Surname into a cell:
>
> =LEFT(A1,FIND(" ",A1)-1)
>
> The rest of it:
>
> =MID(A1,FIND(" ",A1)+1,999)
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >Hi,
> >
> >I have data (names) which appear with Surname first,
> with all other names
> >after that. (all in one cell). For example:
> >
> >Earles Prof Stanley William Edward
> >
> >What I want to do, is split this so that the first word
> (in this case
> >"Earles" stays, and the rest is shifted into a new
> column.
> >
> >Any ideas?
> >
> >PS...I cant get Data, text to columns to work!
> >.
> >
>