Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
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! |
#7
|
|||
|
|||
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! . |
#8
|
|||
|
|||
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" wrote in 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! |
#9
|
|||
|
|||
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |