Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
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
=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.
=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)
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
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