Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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=510058 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split field based on number of characters and space
This gives the left side of the text =LEFT(A1,MIN(30,IF(ISERROR(FIND("
",A1)),30,FIND(" ",A1)))) This gives the right =RIGHT(A1,MIN(30,LEN(A1)-IF(ISERROR(FIND(" ",A1)),30,FIND(" ",A1)))) Have done a bit of testing but 'caveat emptor' best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "william_mailer" <william_mailer.22x0p0_1139409935.9838@excelforu m-nospam.com wrote in message news:william_mailer.22x0p0_1139409935.9838@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=510058 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split field based on number of characters and space
correction for right
=RIGHT(A1,LEN(A1)-IF(ISERROR(FIND(" ",A1)),30,FIND(" ",A1))) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "william_mailer" <william_mailer.22x0p0_1139409935.9838@excelforu m-nospam.com wrote in message news:william_mailer.22x0p0_1139409935.9838@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=510058 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split field based on number of characters and space
If I understood correctly you want to split a cell if the number of characters exceeds 30. And when you split that cell you want to split it at the first space characted within the cell. When you say split I am assuming you want all of the characters to the left of the space in one cell and all of the characters to the right of the space in another. I also assumed you did not want to inlclude the space in either cell. If the cell containing over 30 characters is in cell A1 and the split out cell are B1 and C1. The formula for B1 would be: =IF(LEN(A1)30,LEFT(A1,FIND(" ",A1)-1),"") This would capture all of the characters to the left of the space. The formula for C1 would be: =IF(LEN(A1)30,RIGHT(A1,LEN(A1)-FIND(" ",A1)),"") This would capture all of the characters to the right of the space. Hopefully I understood correctly, let me know if this helps. Thanks, Ray -- RJL0323 ------------------------------------------------------------------------ RJL0323's Profile: http://www.excelforum.com/member.php...o&userid=19456 View this thread: http://www.excelforum.com/showthread...hreadid=510058 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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=510058 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split field based on number of characters and space
william_mailer Wrote: 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 ! The only way I can figure out how to do this is with a UDF Try this for the 'left' side: Function split_at_space(string_to_split As String, split_at As Integer) 'string_to_split is your string 'split_at is the max number characters you want in your string Dim strvar As Variant 'variant used to hold the parsed string array Dim concatstr As String 'variable used to reassemble the parsed string Dim loopcount1, loopcount2 As Integer 'counters 'first check to see if your string has more than 30 characters 'if it does not, set the formulas result to the original string If Len(string_to_split) < 30 Then split_at_space = string_to_split GoTo exit_function End If 'parse the string using the space as a delimiter strvar = Split(string_to_split, " ") 'check ubound to determine if the array has only one element 'if it does, then there are no spaces in your string and the formula will return your original string If UBound(strvar) = 0 Then split_at_space = string_to_split GoTo exit_function End If 'set the first counter to 0 and the concatstr variable to the first element in the array loopcount = 0 concatstr = strvar(0) 'execute a loop which concatenates each element of the array until 'the new strings length is greater than split_at Do Until Len(concatstr) = split_at loopcount = loopcount + 1 concatstr = concatstr & " " & strvar(loopcount) Loop 'execute a loop which concatenates each element of the one less time than the prior loop to drop the last element loopcount2 = 0 concatstr = strvar(0) Do Until loopcount2 = loopcount - 1 loopcount2 = loopcount2 + 1 concatstr = concatstr & " " & strvar(loopcount2) Loop split_at_space = concatstr exit_function: End Function This is designed to allow the user to determine exactly where they want the string to split. BTW, this is pretty quick and dirty coding. -- FrankB ------------------------------------------------------------------------ FrankB's Profile: http://www.excelforum.com/member.php...o&userid=18952 View this thread: http://www.excelforum.com/showthread...hreadid=510058 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split field based on number of characters and space
On Wed, 8 Feb 2006 08:42:55 -0600, william_mailer
<william_mailer.22x0p0_1139409935.9838@excelforu m-nospam.com wrote: 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 You can do this easily with "regular expressions". If the length of the original string will be less than 256 characters, then download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr With your string in A1, use this formula: =REGEX.MID($A$1,".{1,29}(\s|$)",ROWS($1:1)) Copy/drag down as far as needed. If you are breaking the string into columns, change the last argument to COLUMNS($A:A) and copy/drag across. That last argument is merely a counter that tells which slice of max characters = 30 ending with a space. =============================== If your string length might be greater than 255 characters, we can do this using VBA Regular Expressions, which can handle more than 255 characters. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cHARACTERS BEFORE THE SPACE | Excel Worksheet Functions | |||
Remove varying amounts of space characters | Excel Discussion (Misc queries) | |||
read characters starting from the right until a space is reached | Excel Discussion (Misc queries) | |||
How to return # characters based on 2nd instance of value | Excel Worksheet Functions |