Break one column in two in Excel

Problem: You have first and last name in column A and you want to divide into into two columns, that is first name and last name.

You further find out that in some of names in column A has 3 names, for example “Alaxander Joa Pilata”. In this case you want to combined the first two names as first name and leave the last name as the last name

Solution

In Excel 2007, select the column you want to break, in this case column A. Make sure column B and C (that is column next to it) are empty. Goto Data tab in ribbon menu and select Text To Column in “Data Tools” section. Follow the dialog boxes. Select space as delimiter. This will effectively break your column in two or three column depending on how many names

Solving the three names problem

Now lets discuss the three names problem. We are going to use three excel functions to address this problem

ConCatenate
IsText
IF

Concatenate (Excel)

is used to join the join the contents of Cell B and C. Do not forget to add the space in between.
CONCATENATE(B2,” “,C2)

IsText (Excel)

is used to check if the value is a text. It returns for true if it is text, false if it is not text. Remember that it check false for numbers also!

IF (Excel)

If function take 3 argument, first it true or false, second value if true, third value if false
=IF(condition, this value if true, this value if false)

=IF(ISTEXT(D2),CONCATENATE(B2,” “,C3),B2)

This entry was posted in Software Development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s