How to remove blank spaces, vbLf, after and before the result of concatenation?

Question

Input data in first image and result in second, column H. enter image description here

enter image description here

Now read this http://stackoverflow.com/questions/9578397/how-to-remove-leading-or-trailing-spaces-in-an-entire-column-of-excel-worksheet — Grijesh Chauhan, Feb 16 '14 at 12:18
Hi, Grijesh, the problem is delimiter that I use to concatenate cells: vbLf — Cris Reis, Feb 16 '14 at 12:49

Dmitry Pavliv · Accepted Answer · 2014-02-16T15:46:36.007

1

Try this one:

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,CHAR(10)," "))," ",CHAR(10))

or VBA equivalent:

Sub test()
    With Range("D1:D10")
        .Replace Chr(10), " "
        .Value = Evaluate("INDEX(TRIM(" & .Address & "),)")
        .Replace " ", Chr(10)
        .WrapText = True
    End With
End Sub

edited Feb 16 '14 at 15:46

answered Feb 16 '14 at 15:08

Dmitry Pavliv

35,333
13
79
80

score 0 · Answer 2 · answered Feb 16 '14 at 13:46

0

Here is a method of concatenation that will avoid any unnecessary separators:

Public Function StitchValues(rIn As Range) As String
    Dim r As Range, v As Variant
    StitchValues = ""
    For Each r In rIn
        v = r.Text
        If v <> "" Then
            If StitchValues = "" Then
                StitchValues = v
            Else
                StitchValues = StitchValues & vbLf & v
            End If
        End If
    Next r
End Function

So in H1 you would enter:

=StitchValues(B1:E1)

and then turn on text-wrapping, adjust row heights, etc.

answered Feb 16 '14 at 13:46

Gary's Student

95,722
10
59
99

Thank you, Gary,but a need vbLf delimiters, cause in my real sheet are telefone numbers. – Cris Reis Feb 16 '14 at 14:52
The UDF inserts **vbLf** where they are needed, but not at the beginning or end of the concatenation. – Gary's Student Feb 16 '14 at 14:58

How to remove blank spaces, vbLf, after and before the result of concatenation?

2 Answers2