Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate Concatenated First,Last Names
I have a spreadsheet which contains concatenated first and last names within
a cell which I have to separate. The Last Name is denoted by an Upper Case First Letter. An example is BillGates How might a separate the first and last names into separate cells? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate Concatenated First,Last Names
Select the cells you want processed and run:
Sub Separate() Dim iloc As Long, i As Long Dim cell As Range, sStr As String For Each cell In Selection sStr = cell.Value i = Len(sStr) + 1 For iloc = Len(sStr) To 1 Step -1 If UCase(Mid(sStr, iloc, 1)) = Mid(sStr, iloc, 1) Then i = iloc Exit For End If Next If i < Len(sStr) + 1 Then cell.Value = Left(sStr, i - 1) cell.Offset(0, 1).Value = Right(sStr, Len(sStr) - i + 1) End If Next End Sub This leaves the first name in the original cell and puts the last name in the cell to the right. -- Regards, Tom Ogilvy "Al" wrote in message ... I have a spreadsheet which contains concatenated first and last names within a cell which I have to separate. The Last Name is denoted by an Upper Case First Letter. An example is BillGates How might a separate the first and last names into separate cells? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to separate and rearrange names | Excel Worksheet Functions | |||
Separate First, Last Names | Excel Discussion (Misc queries) | |||
Names and Separate Worksheets | Excel Worksheet Functions | |||
Splitting a concatenated string into separate rows... | Excel Worksheet Functions | |||
Separate names into 2 columns? | Excel Discussion (Misc queries) |