Hi,

Am Fri, 7 Mar 2014 08:00:31 -0800 (PST) schrieb :

A colleague has SurnameFirstname in a cell - well, in 900 cells! I am trying to come up with a way of splitting the cell before the uppercase letter. I've tried CODE and realise it's uppercase if its CODE is less than 97 but beyond that, I'm stuck! Once I've got a space or other delimiter in there, it'll be easy!

try it with a macro (if the names are not in column A then modify the
code to suit):

Sub Test()
Dim LRow As Long
Dim rngC As Range
Dim i As Long
Dim myArr As Variant
Dim myStr As String
Dim lenStr As Integer

LRow = Cells(Rows.Count, 1).End(xlUp).Row
myStr = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y ,Z"
myArr = Split(myStr, ",")

For Each rngC In Range("A1:A" & LRow)
For i = LBound(myArr) To UBound(myArr)
lenStr = InStr(2, rngC, myArr(i))
If lenStr 0 Then
rngC.Offset(, 1) = Mid(rngC, lenStr, 99)
rngC = Left(rngC, lenStr - 1)
Exit For
End If
Next
Next
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2