Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Rows based on a number in a field | Excel Worksheet Functions | |||
return value based on number of characters in cell / field | Excel Worksheet Functions | |||
LIMIT THE NUMBER OF CHARACTERS ENTERED IN A FIELD | Excel Worksheet Functions | |||
Deleting rows based on number of characters | Excel Discussion (Misc queries) | |||
Split field based on number of characters and space | Excel Worksheet Functions |