Public Function SplitString(rng As Range)
Dim s As String
s = rng.Value
If Len(s) < 30 Then
SplitString = s
Else
iloc = InStr(1, s, " ", vbTextCompare)
If iloc < 30 And iloc < 0 Then
S1 = Left(s, iloc - 1)
s2 = Mid(s, iloc + 1, 255)
SplitString = S1
Else
S1 = Left(s, 30)
s2 = Mid(s, 31, 255)
SplitString = S1
End If
End If

End Function

usage (assume in B1
=SplitString(A1)

to get the right side

=Right(A1,len(A1)-len(B1))

--
Regards,
Tom Ogilvy

"william_mailer"
<william_mailer.22x2bb_1139412001.6385@excelforu m-nospam.com wrote in
message news:william_mailer.22x2bb_1139412001.6385@excelfo rum-nospam.com...

Hi There,

Thanks for the input but i cant seem to get it working !? I've copied
the code into a function and referred to it on the spreadsheet, but its
just returning #value.......Any idea what i might be doing wrong ?


--
william_mailer
------------------------------------------------------------------------
william_mailer's Profile:
http://www.excelforum.com/member.php...o&userid=31318
View this thread: http://www.excelforum.com/showthread...hreadid=510057