merging text without using a formula

[Guest]

I need to be able to merge two colums of text into one column without lossing
the information and I'm not allowed to use a formula! This is an assignment
and we are allowed to use any resource to find our answer. I hope someone
can give me a hand. Thanks

April

 

[Guest]

Hey, go to the microsoft helper guy, and enter in "CONCATENATE" that should
do it for you! It's not a formula it's a function

 

[Earl Kiosterud]

Whether you use = A1 & B1 or = CONCATENATE(A1, B1), it's a formula. The
first uses the concatenate operator, &, and the second uses the concatenate
function, CONCATENATE( A, B ).

Let's see. You're not allowed to use a formula. How about two formulas?

A macro?

 

[Arvi Laanemets]

Hi

Maybe it's meant, that as result you must have not any formula, but merged
values only.

P.e. you have original values in columns A and B, like A2="John" and B2=
"Little". Into cell (p.e. C2) enter the formula
=A2 & " " B2
which displays "John Little"
Copy the formula to same range of rows in column C, as values in A and B.
Select all cells with formulas in column C, copy them, and them PasteSpecial
as values. Now you don't have any formulas anymore - only merged values
remain in column C.
You can delete both columns A and B after that, or leave them when they are
needed for some another task afterwards.

 

[Dave Peterson]

Maybe a macro solution???

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
myRng.Offset(0, 2).Value = myCell.Value _
& " " & myCell.Offset(0, 1).Value
Next myCell
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Another solution might be to give the workbook to someone else, let them do
whatever it takes to merge those two columns and have them return it to you with
no formulas.

Or...
Say your data is in A1:B10 (two columns of 10 rows).

Copy A1:A10 and paste into C1:C10

Now for each cell in b1:B10, select that cell, copy the contents from the
formula bar (that's important).

Then select C1. Hit F2 to edit it.
Hit a spacebar (to separate the values???)
hit ctrl-v to paste in the copied text.

Then do C2, C3, ...