MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combine 2 text boxes into one


Posted by GLITZ on December 14, 2000 1:33 PM

Kinda simple, but I need help....

I have 2 adjacent columns one with 1st names and the other with last names:
A B C
1 [bill] [smith]
2 [bob] [boyd]
3 [larry] [walker]


What formula can I put in the C column to return the names combined?
like this:

A B C
1 [bill] [smith] [bill smith]
2 [bob] [boyd] [bob boyd]
3 [larry] [walker] [larry walker]

Thank you



Posted by greg on December 14, 2000 1:56 PM


=a1 & " " & b1
Put that in the cell you want combined and that should work if you want more help it is called "concatenation". You can type that in help and get more info.

Posted by GLITZ on December 14, 2000 2:28 PM

=a1 & " " & b1 Put that in the cell you want combined and that should work if you want more help it is called "concatenation". You can type that in help and get more info.

Thank you!
What about the opposite?
Taking a full name that is located in a single cell and dividing it into 2 different cells? (there would be a space in between the names)

Posted by MATT on December 14, 2000 2:48 PM

a1 contains [bill smith]
in b1 type =search(" ",a1,1) returns the value of the blank space (reading left to right)
in c1 type =mid(a1,1,b1) returns [smith]
in d1 type =right(a1,b1) returns [bill]

hope this helps,
matt

Posted by Celia on December 14, 2000 4:21 PM

Probably the easiest way is to use Excel's Text To Columns tool under the Data menu.

Alternatively, the following formulas :-

To get the first word ("Bill") :-
=LEFT(A1,FIND(" ",A1,1)-1)

To get all words except for the first one ("Smith") :-
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

Celia