How do I do the opposite of concatenate?

Question

Level 1

0 points

How do I do the opposite of concatenate?

How do I do the opposite of concatenate?

I have a first name, last name in the same sale (e.g. cell A1 says "Mike Smith"). I want to put the first name in one column and the last name in another (e.g. cell B1 says "Mike" and cell C1 says "Smith").

How do I do this? There are about 500 names and they are all different lengths and first and last names are always separated by a space.

MacBook, Mac OS X (10.5.5)

Posted on Sep 19, 2008 6:27 AM

Reply

Answer 1

KOENIG Yvan

Level 8

41,819 points

Dec 20, 2010 2:33 PM in response to marktfg

Question asked and responded several times.

If original strings are in column B,
Insert
=IF(ISBLANK(B),"",LEFT(B,SEARCH(" ",B)-1))
in column C
and
=IF(ISBLANK(B),"",RIGHT(B,LEN(B)-SEARCH(" ",B)))
in column D

It's just a starting point which assumes that the fiirst name is a single one.

It is difficult to leave a spreadsheet formula recognize that
Jean Jacques Rousseau as "Jean Jacques" and "Rousseau"
and
Jean de la Fontaine as "Jean" and "de la Fontaine"

Yvan KOENIG (from FRANCE vendredi 19 septembre 2008 15:52:39)

Reply

Answer 2

jaxjason

Level 5

5,063 points

Dec 20, 2010 2:33 PM in response to marktfg

=IF(ISBLANK(B),"",LEFT(B,SEARCH(" ",B)-1))
=IF(ISBLANK(B),"",RIGHT(B,LEN(B)-SEARCH(" ",B)))

Yvans equation works great, a little more explanation if you need it...

the base functions you should research and learn for this are RIGHT, LEFT, LEN and SEARCH.

ISBLANK is only really necessary if you might have empty cells in B that have no name. the IF in his equation tests for that and places an empty string as an answer in that case. Otherwise you would most likely see an error show up instead.

If and only if the value in B is not blank, then it runs the other equation, which searched for the space in the value, and uses that position to tell the LEFT command how many characters to grab (1 less than the result of search)

If it is column D, it has to know how many total characters there are in the string, and subtract the number of the position of the space to find out how many characters from the right hand edge of the string to grab.

Hope this helps explain it better if you were still a little baffled,
Jason

Reply