Simple display questions

  • Thread starter Thread starter Mason Pratt
  • Start date Start date
M

Mason Pratt

I have a series of text cells I wish to combine into one cell. Example: In
D4 I wish to put the combination of B1 thru B5. My formula is
=B1&B2&B3&B4&B5. This works however I have 2 quick questions about the
results.



1.. Is there a way to shorten the formula? Sometimes the series is quite
long.
2.. Is there a limit to how long of a result can be displayed? i.e., more
than 256 characters.
Thank you.
 
Here is a sub I use to fix long formulas that get posted. Should work for
your purposes.

Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
x = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(x, y), Cells(z, y))
'Cells(x - 1, y) = mstr & C
mstr = mstr & C
next
Cells(x - 1, y) = mstr
end sub
 
or

Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
x = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(x, y), Cells(z, y))
Cells(x - 1, y) = Cells(x - 1, y) & C
Next
End Sub
 
Mason

A User Defined Function can be employed.

I have enclosed two UDF's. First one will string everything together with no
space. Second will add a comma between each cell string.

There is a limit of 32,767 characters in a cell. However only about 1000 will
show or print.

Copy/paste these to a General Module in your workbook.

If unsure of or unfamiliar with Macros and Functions visit David McRitchie's
website at http://www.mvps.org/dmcritchie/excel/getstarted.htm

Function ConRange(CellBlock As Range) As String
For Each cell In CellBlock
ConRange = ConRange & cell.Value
Next
End Function

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sBuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sBuf = sBuf & cell.text & ","
Next
ConCatRange = Left(sBuf, Len(sBuf) - 1)
End Function

Gord Dibben XL2002
 
Worked perfectly.

A followup quick question. In the first UDF how would I modify it to read
every second cell or every third cell?

Thanks,
Mason

Mason

A User Defined Function can be employed.

I have enclosed two UDF's. First one will string everything together with no
space. Second will add a comma between each cell string.

There is a limit of 32,767 characters in a cell. However only about 1000 will
show or print.

Copy/paste these to a General Module in your workbook.

If unsure of or unfamiliar with Macros and Functions visit David McRitchie's
website at http://www.mvps.org/dmcritchie/excel/getstarted.htm

Function ConRange(CellBlock As Range) As String
For Each cell In CellBlock
ConRange = ConRange & cell.Value
Next
End Function

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sBuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sBuf = sBuf & cell.text & ","
Next
ConCatRange = Left(sBuf, Len(sBuf) - 1)
End Function

Gord Dibben XL2002
Click to expand...
 
Back
Top