Split cell based on uppercase letter

[maccboy]

Hi
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!
Cheers.

 

[Claus Busch]

Hi,

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

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.

 

[maccboy]

Hi

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!

Cheers.

Brilliant!
Thanks!