On Wed, 28 Apr 2010 19:15:48 -0700 (PDT), Raj wrote:

Thanks a ton, Ron. Exactly what I wanted.

Regards,
Raj

Glad to help. Thanks for the feedback.

Looking back at an old thread you've previously posted, where the goal was to
split the string at that point, that could also be done with Regular
Expressions in VBA, with no need to insert a delimiter.

For example:

======================================
Option Explicit
Sub SplitSpecial()
Dim re As Object, mc As Object, m As Object
Const sPat As String = "^(.*?BBA.*?\d{11})\s*(.*)$"
'can omit \s* if do not want to trim leading spaces from
'second substring
Dim c As Range, rg As Range

Set re = CreateObject("vbscript.regexp")
With re
.MultiLine = True
.Pattern = sPat
.ignorecase = False
End With

Set rg = Range("A1:A3")
Range(rg.Offset(0, 1), rg.Offset(0, 2)).ClearContents
For Each c In rg
With c
If re.test(.Value) = True Then
Set mc = re.Execute(.Value)
.Offset(0, 1).Value = mc(0).submatches(0)
.Offset(0, 2).Value = mc(0).submatches(1)
End If
End With
Next c
End Sub
======================================
--ron