Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
I have a column of names first name, middle initial (sometimes), last name.
I have a hard time to separate that middle initial that some of the names have but not all. I tried the Data text to column but have a hard time seperating the middle intitial. Any ideas on how to do it? Thanks much. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
If all names had a middle initial would that solve your problem? If so can
you use a number or character when there is no middle initial. A null character or blank could work?? "kikilein" wrote: I have a column of names first name, middle initial (sometimes), last name. I have a hard time to separate that middle initial that some of the names have but not all. I tried the Data text to column but have a hard time seperating the middle intitial. Any ideas on how to do it? Thanks much. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
Hi kikilein,
Text to columns should work. How are the names delimited? With comma's, spaces, semi-colons? Maybe a mix of all three? Make sure you select all the possibles on the second page of the wizard. The screen at the bottom will give you a preview of how it will look. HTH Martin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
MartinW,
I tried using text to column but I will end up with three columns and the middle column has the middle initial in some cells and the last name from names without the middle initial in other cells. The name list is huge. Below please find a sample of the name list: Joe T. Schmoe Karen K. Canterberry Simone Karter Lois-Martin Fender "MartinW" wrote: Hi kikilein, Text to columns should work. How are the names delimited? With comma's, spaces, semi-colons? Maybe a mix of all three? Make sure you select all the possibles on the second page of the wizard. The screen at the bottom will give you a preview of how it will look. HTH Martin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
Chip Pearson's site deals with this nicely.
Provides a downloadable workbook with examples if you want it. http://www.cpearson.com/excel/FirstLast.htm Gord Dibben MS Excel MVP On Sat, 15 Jul 2006 16:15:02 -0700, kikilein wrote: I have a column of names first name, middle initial (sometimes), last name. I have a hard time to separate that middle initial that some of the names have but not all. I tried the Data text to column but have a hard time seperating the middle intitial. Any ideas on how to do it? Thanks much. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
Hi Gord,
Thanks for the site. Maybe I am totally unable to think the formula through and that is why I can not get it to work. When I paste either of the formulas into into cell B2 (the firstname, middle initial, lastname is in A2) the result I am getting is the same name including firstname, middle initial, lastname. Nothing is being seperated. What am I doing wrong? "Gord Dibben" wrote: Chip Pearson's site deals with this nicely. Provides a downloadable workbook with examples if you want it. http://www.cpearson.com/excel/FirstLast.htm Gord Dibben MS Excel MVP On Sat, 15 Jul 2006 16:15:02 -0700, kikilein wrote: I have a column of names first name, middle initial (sometimes), last name. I have a hard time to separate that middle initial that some of the names have but not all. I tried the Data text to column but have a hard time seperating the middle intitial. Any ideas on how to do it? Thanks much. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
Hi kikilein,
I can certainly understand your problem. You will need to install a macro(s), See http://www.mvps.org/dmcritchie/excel/join.htm Separate the Last term (SepLastTerm), then insert a column between first part and last name, if you want the middle name or initial in it's own column and use SepTerm macro. Link to install macros in a yellow box at top of web page, but it is http://www.mvps.org/dmcritchie/excel....htm#havemacro The macros described on that page can all be found in http://www.mvps.org/dmcritchie/excel/code/join.txt If you have last names with spaces in them you will probably want to join them with an tilde (~) or something first, and then replace the tilde with a space when done. Using Replace (Ctrl+H). i.e. van~Ness van~der~Beck so as not be be confused with a hyphenated names. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "kikilein" wrote in message ... I have a column of names first name, middle initial (sometimes), last name. I have a hard time to separate that middle initial that some of the names have but not all. I tried the Data text to column but have a hard time seperating the middle intitial. Any ideas on how to do it? Thanks much. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
To do this in VBA, you can call the Excel VLookup function from within VBA Assume A1 has your list box, the cell link is B1 you have a range with your lookup table 1 banana, 2 apples etc the this code will give you what you want Sub test() Dim myRange As Range y = Range("B1").Value Set myRange = Range("Table") X = Application.WorksheetFunction.VLookup(y, myRange, 2) End Sub Regards Matt -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514 View this thread: http://www.excelforum.com/showthread...hreadid=561786 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
Here is some code that will act on the selected text Sub ParseNames() Dim myRange As Range Dim myArray(2), CommaCount, X As Integer Set myRange = Selection For Each cell In myRange CommaCount = 0 myArray(1) = 0 myArray(2) = 0 'count commas If Len(cell) = 0 Then GoTo ExitHe For X = 1 To Len(cell) If Mid(cell.Text, X, 1) = "," Then CommaCount = CommaCount + 1 myArray(CommaCount) = X 'store position of comma End If Next X If CommaCount = 1 Then cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1) cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) - myArray(1) - 1) Else cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1) cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) - myArray(2) - 1) cell.Offset(0, 3).Value = Mid(cell.Value, myArray(2) - 1, 1) End If ExitHe Next cell End Sub -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514 View this thread: http://www.excelforum.com/showthread...hreadid=561786 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
Mallycat,
Forgive me, but how am I going to use the code? Please explain (remember I am not that advanced). Where do I paste this code? Is it a macro? Thanks so much. "Mallycat" wrote: Here is some code that will act on the selected text Sub ParseNames() Dim myRange As Range Dim myArray(2), CommaCount, X As Integer Set myRange = Selection For Each cell In myRange CommaCount = 0 myArray(1) = 0 myArray(2) = 0 'count commas If Len(cell) = 0 Then GoTo ExitHe For X = 1 To Len(cell) If Mid(cell.Text, X, 1) = "," Then CommaCount = CommaCount + 1 myArray(CommaCount) = X 'store position of comma End If Next X If CommaCount = 1 Then cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1) cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) - myArray(1) - 1) Else cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1) cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) - myArray(2) - 1) cell.Offset(0, 3).Value = Mid(cell.Value, myArray(2) - 1, 1) End If ExitHe Next cell End Sub -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514 View this thread: http://www.excelforum.com/showthread...hreadid=561786 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
On Sat, 15 Jul 2006 16:15:02 -0700, kikilein
wrote: I have a column of names first name, middle initial (sometimes), last name. I have a hard time to separate that middle initial that some of the names have but not all. I tried the Data text to column but have a hard time seperating the middle intitial. Any ideas on how to do it? Thanks much. You can do this with regular expressions. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Then, assuming the Middle Initial is preceded by a <space, may optionally be terminated by a period (.) and is followed by a <space, you can use the formula: =REGEX.MID(A2,"(?<=\s)\w\.?(?=\s)") If the Middle Initial might be a Middle Name, then: =REGEX.MID(A2,"(?<=\s)\w+\.?(?=\s)") would pick up both. You can also use regular expressions for the first and last names: First Name: =REGEX.MID(A2,"^\w+(?=\s)") Last Name: =REGEX.MID(A2,"\w+$") --ron |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
On Sat, 15 Jul 2006 21:42:14 -0400, Ron Rosenfeld
wrote: On Sat, 15 Jul 2006 16:15:02 -0700, kikilein wrote: I have a column of names first name, middle initial (sometimes), last name. I have a hard time to separate that middle initial that some of the names have but not all. I tried the Data text to column but have a hard time seperating the middle intitial. Any ideas on how to do it? Thanks much. You can do this with regular expressions. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Then, assuming the Middle Initial is preceded by a <space, may optionally be terminated by a period (.) and is followed by a <space, you can use the formula: =REGEX.MID(A2,"(?<=\s)\w\.?(?=\s)") If the Middle Initial might be a Middle Name, then: =REGEX.MID(A2,"(?<=\s)\w+\.?(?=\s)") would pick up both. You can also use regular expressions for the first and last names: First Name: =REGEX.MID(A2,"^\w+(?=\s)") Last Name: =REGEX.MID(A2,"\w+$") --ron I overlooked this in reading your first post: If the First Name and optional Middle Initial are always followed by a comma and <space, then you could use these formulas: FN: =REGEX.MID(A2,"^\w+(?=,\s)") MI: =REGEX.MID(A2,"(?<=,\s)\w\.?(?=,\s)") LN: =REGEX.MID(A2,"\w+$") --ron |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
Thank you all so much for all your replies. I will try one by one to see if
something works for me (I am not so good when it comes to VBA and Macros :-) but will venture this out). I will report back. BTW: the names are listed as follows: first_name middle_initial. last_name (althought the middle initial is missing in some of the names). "Ron Rosenfeld" wrote: On Sat, 15 Jul 2006 21:42:14 -0400, Ron Rosenfeld wrote: On Sat, 15 Jul 2006 16:15:02 -0700, kikilein wrote: I have a column of names first name, middle initial (sometimes), last name. I have a hard time to separate that middle initial that some of the names have but not all. I tried the Data text to column but have a hard time seperating the middle intitial. Any ideas on how to do it? Thanks much. You can do this with regular expressions. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Then, assuming the Middle Initial is preceded by a <space, may optionally be terminated by a period (.) and is followed by a <space, you can use the formula: =REGEX.MID(A2,"(?<=\s)\w\.?(?=\s)") If the Middle Initial might be a Middle Name, then: =REGEX.MID(A2,"(?<=\s)\w+\.?(?=\s)") would pick up both. You can also use regular expressions for the first and last names: First Name: =REGEX.MID(A2,"^\w+(?=\s)") Last Name: =REGEX.MID(A2,"\w+$") --ron I overlooked this in reading your first post: If the First Name and optional Middle Initial are always followed by a comma and <space, then you could use these formulas: FN: =REGEX.MID(A2,"^\w+(?=,\s)") MI: =REGEX.MID(A2,"(?<=,\s)\w\.?(?=,\s)") LN: =REGEX.MID(A2,"\w+$") --ron |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
You haven't really said what you want to do, If you want a column of first names, a column of surnames and a column of initials (when they exist) you could use these formulas in B1, C1 and D1 for data in A1 =LEFT(A1,FIND(" ",A1)-1) =REPLACE(A1,1,FIND(" ",A1)+(ISNUMBER(FIND(".",A1)))*3,"") =IF(ISNUMBER(FIND(".",A1)),MID(A1,FIND(" ",A1)+1,1),"") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=561786 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
daddylonglegs,
I am getting soooo close with your formulas. One is extracting the first name correctly and the other extracts the last name. However, I am still having trouble seperating the middle initial and the formula: =IF(ISNUMBER(FIND(".",A1)),MID(A1,FIND(" ",A1)+1,1),"") is giving me nothing (the cell stays empty. To clarify. I have a long list of names. For example: Joe T. Schmoe Karen K. Canterberry Simone Karter Lois-Martin Fender I would like to end up with three columns. One with firstname, one with middle initial (if there is one) and one with the lastname. Thank you very much for trying to help (all of you). "daddylonglegs" wrote: You haven't really said what you want to do, If you want a column of first names, a column of surnames and a column of initials (when they exist) you could use these formulas in B1, C1 and D1 for data in A1 =LEFT(A1,FIND(" ",A1)-1) =REPLACE(A1,1,FIND(" ",A1)+(ISNUMBER(FIND(".",A1)))*3,"") =IF(ISNUMBER(FIND(".",A1)),MID(A1,FIND(" ",A1)+1,1),"") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=561786 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
On Sun, 16 Jul 2006 07:38:01 -0700, kikilein
wrote: Thank you all so much for all your replies. I will try one by one to see if something works for me (I am not so good when it comes to VBA and Macros :-) but will venture this out). I will report back. BTW: the names are listed as follows: first_name middle_initial. last_name (althought the middle initial is missing in some of the names). Then, after downloading and installing morefunc.xll as I posted previously, just use these formulas: First Name: =REGEX.MID(A2,"^\w+(?=\s)") Middle Initial: =REGEX.MID(A2,"(?<=\s)\w\.?(?=\s)") Last Name: =REGEX.MID(A2,"\w+$") --ron |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
separating text from cells
Thanks again to everyone for your response. I have solved the problem in the
following way using David McRitchie's suggestion. To recap the issue (after all there was a lot of back and forth and not really in order)the following name list was to be separated into three separate columns (first name, middle initial, last name). Joe T. Schmoe Karen K. Canterberry Simone Karter Lois-Martin Fender I created a macro with the following text: Sub SepLastTerm() 'David McRitchie 1998-08-20 [Ctrl+l] documented in ' http://www.mvps.org/dmcritchie/excel/join.htm 'Separate the last term from remainder, as in separating 'lastname from firstname 'Work on first column, cell to right must appear to be blank Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlAutomatic 'On Error Resume Next Dim iRows As Long, mRow As Long, ir As Long iRows = Selection.Rows.Count Set lastcell = Cells.SpecialCells(xlLastCell) mRow = lastcell.Row If mRow < iRows Then iRows = mRow 'not best but better than nothing For ir = 1 To iRows If Len(Trim(Selection.Item(ir, 1).Offset(0, 1))) < 0 Then iAnswer = MsgBox("Found non-blank in adjacent column -- " _ & Selection.Item(ir, 1).Offset(0, 1) & " -- in " & _ Selection.Item(ir, 1).Offset(0, 1).AddressLocal(0, 0) & _ Chr(10) & "Press OK to process those than can be split", _ vbOKCancel) If iAnswer = vbOK Then GoTo DoAnyWay GoTo terminated End If Next ir DoAnyWay: For ir = 1 To iRows If Len(Trim(Selection.Item(ir, 1).Offset(0, 1))) < 0 _ Then GoTo nextrow checkx = Trim(Selection.Item(ir, 1)) L = Len(Trim(Selection.Item(ir, 1))) If L < 3 Then GoTo nextrow '-- this is where SepLastTerm differs from SepTerm For im = L - 1 To 2 Step -1 If Mid(checkx, im, 1) = " " Then Selection.Item(ir, 1) = Left(checkx, im - 1) Selection.Item(ir, 1).Offset(0, 1) = Trim(Mid(checkx, im + 1)) GoTo nextrow End If Next im nextrow: Next ir terminated: Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic Application.ScreenUpdating = True End Sub This macro took the last name and inserted it into a separate column. Then I was left with the first name and sometimes a middle initial in one column. I separated that one using the "text to column" feature. Gord, I realized that it was David's macro that I used and that I got to work. Sorry, but many thanks for you help as well. Now, after all this, I have to jump over to the Word section because I need to do exactly the same thing just in Word. I copied the table from Word into Excel thinking that I could sort it better there, but did not realize that there are now merged cells and copying it back into Word gave me trouble. Oh may.... "kikilein" wrote: I have a column of names first name, middle initial (sometimes), last name. I have a hard time to separate that middle initial that some of the names have but not all. I tried the Data text to column but have a hard time seperating the middle intitial. Any ideas on how to do it? Thanks much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells within a list to display a certain text but have different v | Excel Discussion (Misc queries) | |||
Summing cells that contain numbers and text | Excel Discussion (Misc queries) | |||
vlookup on large text in cells | Excel Worksheet Functions | |||
Text shown up in other cells everytime a text is entered in 1 cell | Excel Discussion (Misc queries) | |||
I want Excel to allow cells with formulas and unrelated text | Excel Discussion (Misc queries) |