Populate cell with multiple non-blank text values


L

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!!!
 
Ad

Advertisements

D

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
 
G

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
 
Ad

Advertisements

D

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top