concatenate text cells- add space

  • Thread starter Thread starter Vivian
  • Start date Start date
V

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?
 
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?
 
=TRIM(IF(Q2<>""," "&Q2,"")&IF(R2<>""," "&R2,"")&IF(S2<>"","
"&S2,"")&IF(T2<>""," "&T2,"")&IF(U2<>""," "&U2,"")&IF(V2<>""," "&V2,""))
 
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
 
Vivian

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

Will remove all spaces except one between each text string.

Gord Dibben Excel MVP XL2002
 
Aladin,

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

:=)

Anders
 
Back
Top