Pick values from multiple cells and paste output in one cell

Question

I have a dataset which looks like:

A   0.998315185
B   0.232720507
C   0.010558964
D   0.004246209
E   0.002552556

I want to paste below output in one cell as:

0.998315185A+0.232720507B+0.010558964C+0.004246209D+0.002552556E

So that I can copy this and use it elsewhere.

HI ROY. Please edit your question to remove the SHOUTY capital letters. — CallumDA, Apr 03 '17 at 12:23
How big is your sample? If it's only a few cells, you can manually concatenate with the ampersand (&): `=A2&"A +"&A3&"B +"` etc. — Josh Friedlander, Apr 03 '17 at 12:33
Google Sheets is very useful in such a case. Just one formula `=join("+", arrayFormula(filter(B:B&A:A, B:B&A:A <> "")))` is enough. Why not use Google Sheets? — Sangbok Lee, Apr 03 '17 at 15:17

score 1 · Answer 1 · 2017-04-03T13:10:26.287

Use the newer TEXTJOIN function as an array formula with CSE.

=TEXTJOIN("+", TRUE, B2:INDEX(B:B, MATCH(1E+99, B:B))&A2:INDEX(A:A, MATCH(1E+99, B:B)))

¹ _{The TEXTJOIN was introduced with Excel 2016 in the following versions:Excel for Android phones, Excel Mobile, Excel 2016 with Office 365, Excel 2016 for Mac, Excel Online, Excel for iPad, Excel for iPhone and Excel for Android tablet.}

score 0 · Accepted Answer · answered Apr 03 '17 at 12:58

You may try this User Defined Function. Use this function in a cell on the sheet like this...

=CombineData(A1:B5)

User Defined Function:

Function CombineData(ByVal Rng As Range) As String
Dim i As Long, j As Long
Dim str As String
Dim cell As Range
For i = 1 To Rng.Rows.Count
    For j = Rng.Columns.Count To 1 Step -1
        If str = "" Then
            str = Rng.Cells(i, j)
        Else
            str = str & Rng.Cells(i, j)
        End If
    Next j
    str = str & "+"
Next i
CombineData = str
End Function

Pick values from multiple cells and paste output in one cell

2 Answers2