Split field based on number of characters and space
 

I have a string of text in a cell. I want to split the cell based on
anything over 30 characters. But I want to also split on a space. So
basically the first space before I reach 30 characters limit. Hope I
explained well !! Any help would be great !

Thanks


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

Split field based on number of characters and space
 

s = cell.Value
if len(s) < 30 then exit sub
iloc = Instr(1,s," ",vbTextCompare)
if iloc < 30 and iloc < 0 then
s1 = left(s,iloc-1)
s2 = mid(s,iloc+1,255)
else
s1 = left(s,30)
s2 = Mid(s,31,255)
end if

--
Regards,
Tom Ogilvy

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

I have a string of text in a cell. I want to split the cell based on
anything over 30 characters. But I want to also split on a space. So
basically the first space before I reach 30 characters limit. Hope I
explained well !! Any help would be great !

Thanks


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

Split field based on number of characters and space
 

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

Split field based on number of characters and space
 

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

Split field based on number of characters and space
 

My explanation was crap, i do apologise !!

If the string is longer than 30 characters, I need the text before the
last space before the 30 character limit.........

Example:

String: This is a massively long string of text and I want to split it

Result1: This is a massively long
Result2: string of text and I want to split it

Note: If I split at 30 characters I break a word so I want to split at
the previous space !

Hope this makes sense !


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

Split field based on number of characters and space
 

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

End Function


--
Regards,
Tom Ogilvy

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

My explanation was crap, i do apologise !!

If the string is longer than 30 characters, I need the text before the
last space before the 30 character limit.........

Example:

String: This is a massively long string of text and I want to split it

Result1: This is a massively long
Result2: string of text and I want to split it

Note: If I split at 30 characters I break a word so I want to split at
the previous space !

Hope this makes sense !


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

Split field based on number of characters and space
 

Tom,

Thats perfect, thanks a million for your help !


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