Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns using capital letters
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns using capital letters
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. "Josh Craig" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to columns using capital letters
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 -------------- Jacob Skaria |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change small letters to capital letters | Excel Discussion (Misc queries) | |||
how do i turn all letters into capital letters? | Excel Discussion (Misc queries) | |||
Text to Coloumns, by consecutive capital letters | Excel Worksheet Functions | |||
Capital Letters Only | Excel Discussion (Misc queries) | |||
Capital Letters | Excel Worksheet Functions |