Populate cell with multiple non-blank text values

[Leadfoot]

I am WAY out of my element with what I am trying to do with Excel and coulduse some help.

My spreadsheet has multiple columns of text data. For each row, some fields are populated, and some aren't. I am trying to populate a cell in anotherlocation with just the non-blank values for each row, with the text separated by returns. The cell is formatted for word wrap. So it's simply:

TEXT
TEXT
TEXT

(but in a single cell)

I tried using the following: =A1&CHAR(10)&A2&CHAR(10)...A20 - but of course each blank cell simply leads to tons of extra returns that I don't want.I see that there are ISBLANK and ISTEXT functions, but I don't know how touse them in my function. I'm hoping that I can perform a check on each cell and ignore those that are blank. Is it possible? Thanks in advance for any help!!!

 

[dcxtxtxtxt]

I am WAY out of my element with what I am trying to do with Excel and could use some help.



My spreadsheet has multiple columns of text data. For each row, some fields are populated, and some aren't. I am trying to populate a cell in another location with just the non-blank values for each row, with the text separated by returns. The cell is formatted for word wrap. So it's simply:

TEXT

TEXT

TEXT



(but in a single cell)



I tried using the following: =A1&CHAR(10)&A2&CHAR(10)...A20 - but of course each blank cell simply leads to tons of extra returns that I don't want. I see that there are ISBLANK and ISTEXT functions, but I don't know how to use them in my function. I'm hoping that I can perform a check on each cell and ignore those that are blank. Is it possible? Thanks in advance forany help!!!

-----------------------
I'm a little confused with your rows and columns. It sounds as if you wantto accumulate across the rows but your formula seems to be going down the A column. The formula below is accumulating down a column but the idea will work across a row as well.

It's a bit awkward but change your current formula by replacing each CHAR(10) with IF(LEN(the cell you are looking at)=0,"",CHAR(10))
=A1&IF(LEN(A1)=0,"",CHAR(10))&A2&IF(LEN(A2)=0,"",CHAR(10))&A3&...A20

This only puts ib a line feed if the length of the text is nore than 1.

dcxt

 

[Ron Rosenfeld]

I am WAY out of my element with what I am trying to do with Excel and could use some help.

My spreadsheet has multiple columns of text data. For each row, some fields are populated, and some aren't. I am trying to populate a cell in another location with just the non-blank values for each row, with the text separated by returns. The cell is formatted for word wrap. So it's simply:

TEXT
TEXT
TEXT

(but in a single cell)

I tried using the following: =A1&CHAR(10)&A2&CHAR(10)...A20 - but of course each blank cell simply leads to tons of extra returns that I don't want. I see that there are ISBLANK and ISTEXT functions, but I don't know how to use them in my function. I'm hoping that I can perform a check on each cell and ignore those that are blank. Is it possible? Thanks in advance for any help!!!

This general problem is best solved using a User Defined Function (UDF). Here is one that allows you to use any Separator (including Char(10)).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=MultiConcat(A1:A10, CHAR(10))

in some cell.

=====================================
Option Explicit
Function MultiConcat(rg As Range, Optional Separator As String = "")
Dim c As Range
Dim s() As String
Dim i As Long
ReDim s(1 To WorksheetFunction.CountA(rg))
For Each c In rg
If Len(c.Text) > 0 Then
i = i + 1
s(i) = c.Text
End If
Next c

MultiConcat = Join(s, Separator)

End Function
=====================================

 

[GS]

I hate reading/writing directly to ranges and so if I may offer from my
UDF library...

Function JoinRowValues(RngRef As Range, Optional Delimiter$ = ",")
Dim vDataIn, vDataOut(), n&
vDataIn = RngRef: ReDim vDataOut(1 To UBound(vDataIn, 2))
For n = 1 To UBound(vDataIn, 2)
If Not vDataIn(1, n) = Empty Then _
vDataOut(n) = vDataIn(1, n) Else vDataOut(n) = vbNull
Next 'n
JoinRowValues = Join(Filter(vDataOut, vbNull, False), Delimiter)
End Function 'JoinRowValues()

Function JoinColValues(RngRef As Range, Optional Delimiter$ = ",")
Dim vDataIn, vDataOut(), n&
vDataIn = RngRef: ReDim vDataOut(1 To UBound(vDataIn))
For n = 1 To UBound(vDataIn)
If Not vDataIn(n, 1) = Empty Then _
vDataOut(n) = vDataIn(n, 1) Else vDataOut(n) = vbNull
Next 'n
JoinColValues = Join(Filter(vDataOut, vbNull, False), Delimiter)
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[dcxtxtxtxt]

I am WAY out of my element with what I am trying to do with Excel and could use some help.



My spreadsheet has multiple columns of text data. For each row, some fields are populated, and some aren't. I am trying to populate a cell in another location with just the non-blank values for each row, with the text separated by returns. The cell is formatted for word wrap. So it's simply:



TEXT

TEXT

TEXT



(but in a single cell)



I tried using the following: =A1&CHAR(10)&A2&CHAR(10)...A20 - but of course each blank cell simply leads to tons of extra returns that I don't want. I see that there are ISBLANK and ISTEXT functions, but I don't know how to use them in my function. I'm hoping that I can perform a check on each cell and ignore those that are blank. Is it possible? Thanks in advance forany help!!!

Here is a short UDF which works for any area vertical or horizontal. It has the virtue of being easy to understand. Copy it into Module 1

Function conc(area)
h = ""
For Each x In area
If x <> "" Then h = h & x & Chr(10)
Next
conc = h
End Function

It sticks a line feed onto the end of everything. If this is a problem,replace the line conc = h with
If h = "" Then conc = h Else conc = Left(h, Len(h) - 1)