Hi Ritesh,


How is your VBA?

A quick google search found the following:
[http://www.pcreview.co.uk/forums/split-astring-using-capital-letter-identifier-t3855968.html]

The first loops through the word, checking if the letter it is looking at is an Uppercase letter [UCase()] and if so adds a space between it.

'Usage
=SplitWords(A1)

Function SplitWords(strData) As String
  Dim intTemp As Integer
  SplitWords = Left(strData, 1)
  For intTemp = 2 To Len(Trim(strData))
    If Mid(strData, intTemp, 1) = UCase(Mid(strData, _
      intTemp, 1)) Then SplitWords = SplitWords & " "
      SplitWords = SplitWords & Mid(strData, intTemp, 1)
  Next
End Function

--

The next checks the ASCII value [Asc()]. If it is in the range of an Uppercase letter (A - 65, Z - 90) split it.

(http://www.asciitable.com/)

Function splitcap(wholestr)
  splitstr = ""
  For i = 1 To Len(wholestr)
    currchr = Mid(wholestr, i, 1)
    splitstr = splitstr & IIf(Asc(currchr) < 91 And i > 1, " ", "") &
    currchr
  Next i
  splitcap = splitstr
End Function

--

This one uses string matching

Function SplitOnCaps(S As String) As String
  Dim X As Long
  SplitOnCaps = S
  For X = Len(SplitOnCaps) To 2 Step -1
    If Mid(SplitOnCaps, X, 1) Like "[A-Z]" Then
      SplitOnCaps = Left(SplitOnCaps, X - 1) & " " & Mid(SplitOnCaps, X)
    End If
  Next
End Function

--

Will there only be ever 1 uppercase to split on? This will do it for any, an example was "FirstnameMiddlenameLastname"

=====================================
Option Explicit
Function SplitOnCaps(s As String) As String
  Dim re As Object
  Set re = CreateObject("vbscript.regexp")
  re.Global = True
  re.Pattern = "([a-z])([A-Z])"
  SplitOnCaps = re.Replace(s, "$1 $2")
End Function
======================================

--
Each uses a different method and is useful for showing how different in built functions work.

I haven't tested these but the appear to be fine, many thanks to the other guys in the forum for writing this, saved me doing it :p

Regards
Alex