Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Separate cell text FirstnameLastname into two columns
How can I separate a column of cells containing a single text string in each[these are names formated as a string with no delimiter as in "FirstLast" with the first letter of first and last name capitalized and the rest lower case...] into two columns of text called Firstname and Lastname? Thanks!:) -- drewannie ------------------------------------------------------------------------ drewannie's Profile: http://www.excelforum.com/member.php...o&userid=36079 View this thread: http://www.excelforum.com/showthread...hreadid=558631 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Separate cell text FirstnameLastname into two columns
Hi To separate one column in 2. Assuming Column A contains Lastname, firstname. Column B must be empty if not insert a column to create and empty columnB. Once completed click on column A (this should highlight Columns), from there go to menu ----Data ----Text to Columns... Choose the option Delimited and then click on next choose the option space. Click on next and then finish and everything should be separated into 2 columns. THanks Denis -- jetted ------------------------------------------------------------------------ jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532 View this thread: http://www.excelforum.com/showthread...hreadid=558631 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Separate cell text FirstnameLastname into two columns
Hi I forgot if the name in the cell follow this format (no space between firstname and lastname) JohnSmith MarySmith BabySmith Assuming the name are in column A and column B is empty you could you use the following code: Sub separate_firstname_lastname() Dim trouve_ucase As String flag1 = "NO" rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row For i = 1 To rowcount Range("a" & i).Select entirename = ActiveCell.Value check_len = Len(entirename) check_len = check_len For j = 1 To check_len trouve_ucase = Mid(entirename, check_len - j, 1) If trouve_ucase = "A" Or trouve_ucase = "B" Or trouve_ucase = "C" Or trouve_ucase = "D" _ Or trouve_ucase = "E" Or trouve_ucase = "F" Or trouve_ucase = "G" Or trouve_ucase = "H" _ Or trouve_ucase = "I" Or trouve_ucase = "J" Or trouve_ucase = "K" Or trouve_ucase = "L" _ Or trouve_ucase = "M" Or trouve_ucase = "N" Or trouve_ucase = "O" Or trouve_ucase = "P" _ Or trouve_ucase = "Q" Or trouve_ucase = "R" Or trouve_ucase = "S" Or trouve_ucase = "T" _ Or trouve_ucase = "U" Or trouve_ucase = "V" Or trouve_ucase = "W" Or trouve_ucase = "X" _ Or trouve_ucase = "Y" Or trouve_ucase = "Z" And flag1 = "NO" Then number1 = (check_len - j) flag1 = "YES" firstname = Left(entirename, number1 - 1) lastname = Right(entirename, number1 - 1) ActiveCell = firstname ActiveCell.Offset(0, 1).Select ActiveCell = lastname GoTo line1: End If Next j line1: Next i End Sub -- jetted ------------------------------------------------------------------------ jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532 View this thread: http://www.excelforum.com/showthread...hreadid=558631 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Separate cell text FirstnameLastname into two columns
On Wed, 5 Jul 2006 15:08:42 -0500, drewannie
wrote: How can I separate a column of cells containing a single text string in each[these are names formated as a string with no delimiter as in "FirstLast" with the first letter of first and last name capitalized and the rest lower case...] into two columns of text called Firstname and Lastname? Thanks!:) Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ Then with A1: FirstnameLastname Firstname: =REGEX.MID(A1,"[A-Z][^A-Z]+") Lastname: =REGEX.MID(A1,"[A-Z][^A-Z]+",2) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate text within a cell | Excel Discussion (Misc queries) | |||
How can I make an excel cell equal to the value of a frame object text box | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Can I use Excel to match text data from 2 separate columns ? | Excel Worksheet Functions | |||
Help inserting a Cell Value in a Text Cell | Excel Worksheet Functions |