How to separate 2 words in a cell and put in 2 col next

[vietdieu]

Hi guys,

I have a list, each cell in A:A the cell have text of 2 words separated by blanks. How can I make formula for col B and C so each have a word. If there's duplicate then C would be blank.

Example

A1 = "Apples Bear"
A2 = "Heineken Champagne"
A3 = "Pear Pear"


I need formula for B and C col so:
B1 = "Apples" C1="Bear"
B2= "Heineken" C2 = "Champagne"
B3 = "Pear" C3=""



Thanks & hve great weekend.

[jeffreybrown]

How about...

B1 copied down >> =LEFT(A1,FIND(" ",A1)-1)
C1 copied down >> =TRIM(SUBSTITUTE(A1,B1,""))

Data Range

	A	B	C
1	Apples Bear	Apples	Bear
2	Heineken Champagne	Heineken	Champagne
3	Pear Pear	Pear

[vietdieu]

You save me a whole day :D
Thanks so much

[jeffreybrown]

You are very welcome and thanks for the feedback!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.