Cleanly Trimming text...

[Robert.Cordani]

To start with, I am creating a string, based on concatenating a large
number of attributes(cells).
Essentially using this formula for that:

=IF(ISBLANK(A2),"",A2&" ")&IF(ISBLANK(B2),"",B2&" ")...

So if you have a better way, I like to learn it.

My current question revolves around this background.
I need to limit the string to under 255.
I can find a few way to trim these, I am leaning towards the MID
function, but if I simply chop at 255 it will undoubtedly chop off mid
attribute or even mid word.

I even went as far as trying to use the search function to get the
location of a space closest to 255, but search and find seem to ONLY
look for the first instance of a character, there is no way to specify
last (or better yet search right to left).

So any suggestions out there?
TIA

 

[excelent]

if i got u right, try :

=A2&" "&B2&" "&C2&" "&D2&" "&2&.....

 

[HotRod]

=LEFT((A2 & B2 & C2),255)

 

[Robert.Cordani]

Okay I like the LEFT method, better then the MID method, but it still
will not chop off gracefully. I would rather have 8 attributes in the
string taking up 240 characters, then 9 attributes at 255 with the 9th
attribute being cut off.

 

[Tim Williams]

'*******************************
Function ConcatLimit(rng)
Const LIMIT As Integer = 255
Dim c As Range
Dim s As String, v As String

s = ""
For Each c In rng
v = c.Value
If Len(s) + 1 + Len(v) <= 255 Then
If v <> "" Then
If s <> "" Then s = s & " "
s = s & v
End If
Else
Exit For
End If
Next c
ConcatLimit = s
End Function
'*******************************

Lightly tested
Tim

 

[Robert.Cordani]

Hmmm, never used functions in Excel before (didn't realize you could
actually)... this looks like it might be just the concept I am looking
for. Thank you.