using the '&' and keeping the spaces

[Guest]

I'm trying to use the '&' function to assemble a line of data from multiple
cells, but i need to have spaces in the line of data for the spots where
there is no data in the cooresponding cell.

 

[JulieD]

Hi

then try
=IF(LEN(A1)>=1,A1," ")&" " & IF(LEN(B1)>=1,B1," ")
etc

Cheers
JulieD

 

[Guest]

Try this"
=FirstCell&" "&SecodCell&" "&ThirdCell ...

Note that there is a blank space between the quotation marks.

tj

 

[Gord Dibben]

durrant

Make life easier by using a User Defined Function which ignores blank cells in
the range.

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

Usage is: =ConCatRange(A1:A10) adjust range to suit.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP