concatenate text cells- add space

[Vivian]

I have 6 fields of text I want to concatenate AND I want
to add a space between each field. How do I do this?

 

[Vivian]

I found the following formula in the worsheet function
section of this group.
=Q2&" " &R2&" "&S2&" "&T2&" "&U2&" "&V2
It works great. Now I would like to refine it a little.
With some entries one or two of the fields are blank. How
do I tell Excel to ignore the field if it is blank? In
other words, don't put a space if this field is blank?

 

[Aladin Akyurek]

=TRIM(IF(Q2<>""," "&Q2,"")&IF(R2<>""," "&R2,"")&IF(S2<>"","
"&S2,"")&IF(T2<>""," "&T2,"")&IF(U2<>""," "&U2,"")&IF(V2<>""," "&V2,""))

 

[Gord Dibben]

Vivian

=A1 & " " & B1 & " " & C1 etc.

OR User Defined Function copy/pasted to a general module in your workbook.

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:A6) entered in a cell.

Gord Dibben Excel MVP XL2002

 

[Gord Dibben]

Vivian

=TRIM(Q2&" " &R2&" "&S2&" "&T2&" "&U2&" "&V2)

Will remove all spaces except one between each text string.

Gord Dibben Excel MVP XL2002

 

[Anders S]

Aladin,

Maybe not totally ridiculous, I just learned that VBA TRIM is different from
Excel TRIM, it just removes leading and trailing spaces.

:=)

Anders