How to merge 2 columns in Excel with a space

I have a some basic columns in Excel like this:

Full Name | First Name | Last Name | Billy | Bob | Sue | Anne | Super | Man | Joe |

I want to merge the first name and last name together into the Full Name column, separated by a space.

Is there a macro or something that could do that?

Also in the case of Joe who doesn't have a Last name listed, could it just put Joe without a space at the end?

3 Answers

Try this in cell A2:

=TRIM(CONCATENATE($B2," ",$C2))

The trim should take care of that pesky space.

You can do this with a formula, e.g.:

=A1&" "&B1

This is joining A1 to what ever is quoted (in this case a space) with B1.

To handle a missing first or last name, you could use an IF statement.

You could use this formula, assuming that Full Name is in column A, First Name in column B and Last Name in column C, and you want the first name first.

=IF(OR(B2="",C2=""), B2&C2, B2&" "&C2)

Will work if either First Name or Last Name is missing.

EDIT: Overlooked a much simpler one to handle the spaces (credit to Wizard Prang)

=TRIM(B2&" "&C2)