text to columns using capital letters

[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.

 

[dhstein]

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.

 

[Jacob Skaria]

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