Hi, we have a cell of data from a multi select picklist (the first word is the primary and the second word is the secondary):
For example
Apple; Banana
We need to have 2 columns for primary and secondary.
Is there a way to use a formula to capture the first word before the semi colon and populate the new cell? And then for the word after the semi colon for the new cell in the next column.
Thanks,
Charles
Hi Charle,
Welcome to the excel forum...
Assuming you will have only 2 type of category and you have 'Apple; Banana' in cell A1. So use the following function in B1 & C1 to get the desired result
=LEFT(A1,SEARCH(";",A1,1)-1) ---- will return Apple
=RIGHT(A1,SEARCH(";",A1,1)) ---- will return Banana
Let me know if you are looking for the same and it helps you.
Thanks
Nisha Dhawan
If you like my answer please click on * Add Reputation
"If you can dream it, You can do it"
Using a formula...
A2 = Apple; Banana
Enter this formula in B2:
=IF(A2="","",LEFT(A2,FIND(";",A2)-1))
Enter this formula in C2:
=MID(A2,LEN(B2)+3,20)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Sorry, I should have clarified, there are about 40 different "categories" types of fruit. They can choose either 1 fruit, or any two.
So how will they decide that which one or two has to pick?
The one that is before the semi colon is the primary topic, the one after the semi colon is the secondary topic. If there is only 1, then it is the primary topic be default.
So in that case, can you try the above given functions once. It seems they will meet your criteria.
OK, try these...Originally Posted by charlesonmission
B2:
=IF(COUNT(FIND(";",A2)),LEFT(A2,FIND(";",A2)-1),T(A2))
C2:
=MID(A2,LEN(B2)+3,20)
That did it, thanks Tony!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks