text to columns using capital letters

  • Thread starter Thread starter Josh Craig
  • Start date Start date
J

Josh Craig

I had a bit of a look but couldn't find an answer to this question:

How do I go about separating text into columns using a capital letter as the
delimiter?

For example:

So 'JoshCraig' in A1 becomes 'Josh' in A1 and 'Craig' in B1.

The problem arises because my data doesn't have spaces between first and
last names.
 
You have your work cut out for you. assume JoshCraig is in cell A5
Put the formula =lower(a5) in cell B5

Now you need to strip off each character 1 at a time - and convert to code
(=Code) Then you can compare corresponding character (J to j - these
will match which is why you need the =Code function). It can be done, but
it's not pretty so unless you have a lot of data, you might consider the
manual solution.
 
Dear Craig

Please use the below function. Launch VBE using Alt+F11. Insert a Module.
Paste the below code and save. Get back to worksheet. Select this function
under UserDefinedFunction.

=SplitTextbyCase(A1)

Function SplitTextbyCase(strText)
Dim intTemp
Dim intLen
intTemp = 2
intLen = Len(strText)
Do
If Asc(Mid(strText, intTemp, 1)) < 97 And Asc(Mid(strText, intTemp, 1)) > 64
Then
strText = Trim(Left(strText, intTemp - 1)) & " " & Trim(Mid(strText, intTemp))
intTemp = intTemp + 1
intLen = Len(strText)
End If
intTemp = intTemp + 1
Loop Until intTemp > intLen
SplitTextbyCase = strText
End Function


If this post helps click Yes
 
Back
Top