CODEWITHSUNDEEP
excelfunctionexcel-formulaexcel-2010
user9269270
user9269270

Reputation: 21

excel function to combine cells into single line of text?

I'm new to stack overflow so I apologize if this is a horrendously stupid question. I am wondering if there is a function or way to code a function in excel that will combine a column of cells with plain text and convert them into one cell with the text on a single line? Specifically I want to convert a column of random numbers into a single line of text and insert SPACE+AND+SPACE between them.

Ex.

15133484

12345188

12345888

to

15133484 AND 12345188 AND 12345888

Currently I am copying and pasting all this information into google and then into Word and using find/replace and it is taking forever everytime. If it is possible to just get Excel to do this for me that would be amazing.

Thanks!

Upvotes: 1

Views: 7876

Answers (5)

Samsdj
Samsdj

Reputation: 1

I assume you want to merge the data in the 3 cells into a single cell with a space between the 3 data set.

If that is the case then you can do it simply by using the Concatenate function in excel.

enter image description here

In the above example, you have data in Cells A1, A2 & A3.

Cell C1 has the merged data. As you can see, we have used CONCATENATE Function.

The space has been defined in Double quotes. So if you need a Hyphen (-), you can put that in Double Quotes with space “ - ” and it will display the result with Sanjay - Singh - Question

Hope this helps.

Upvotes: 0

EunosNB
EunosNB

Reputation: 41

A little late, but still:

Reference here

Step 1:

=concatenate(transpose(rngBeg:rngEnd & " AND "))

Step 2: highlight the transpose statement and then press F9, which substitutes the actual values for the formula.

Step 3: Remove the curly braces, { }, from the formula. The cell will display the range of reference cells combined with whatever separator chosen after the ampersand sign.

Not a "live" formula, but still far easier than manually concatenating a range of values.

Upvotes: 1

be_good_do_good
be_good_do_good

Reputation: 4441

use concat function if you can add an additional column in the excel like this:

=CONCAT(D3:E5)

Attached sample image with input, additional column, output and formulaenter image description here

Upvotes: 0

vigilent
vigilent

Reputation: 1

Press ALT+F11 to open Microsoft Visual Basic for Applications, Insert-> Module Paste this:

Function Combine(WorkRng As Range, Optional Sign As String = " AND ") As String    
Dim Rng As Range    
Dim OutStr As String

For Each Rng In WorkRng    
    If Rng.Text <> "," Then    
        OutStr = OutStr & Rng.Text & Sign    
    End If    
Next

Combine = Left(OutStr, Len(OutStr) - 5)

End Function

In any cell type =Combine(Range)

i.e.

=Combine(A1:A500)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

If you have Office 365 Excel use TEXTJOIN():

=TEXTJOIN(" AND ",TRUE,A:A)

otherwise one would have to use:

=A1 & " AND " & A2 & " AND " & A3

Or one can use a helper column, B1 put:

=A1

put this in B2 and copy down:

=IF(A2<>"",B1 & " AND " & A2,B1)

And grab the last cell in column B.

Upvotes: 3

Related Questions