Split field based on number of characters and space

[william_mailer]

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

 

[Bernard Liengme]

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"

 

[Bernard Liengme]

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"

 

[RJL0323]

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

 

[william_mailer]

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 !

 

[FrankB]

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.

 

[Ron Rosenfeld]

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