• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Placing text from comma separated in a single cell in specific columns [SOLVED]

T

tonymjr

New Member
I have a set of terms that are separated by a comma in a single cell associated with a user's name. How can I pull the comma separated terms and move them into individual cells in the same row? The terms will match the column header. What formula can I create to find and move the term to the cell on the same row with a matching column title?


Any help is appreciated.
 
Tonymjr


Select the range

Goto Data, Text to Columns

Delimited

Select a Comma as the delimiter

Apply
 
Hi Hui,

Thanks for the instructions. How can I get each term into a corresponding column titled exactly as the term? I have 20 columns and each row has approximately 5-10 terms that need to go into a corresponding column.


Ideas?

Thanks,

Tony
 
Hi Tony ,


Try this :


=IF(ISNUMBER(SEARCH(","&B$1&",",","&$A2&",")),B$1,"")


Enter this formula in B2 ; copy across and down.


I have assumed your titles are in row 1 , starting with B1 , and your comma separated text is in column A , starting with A2.


I also hope that the comma separated text does not have spaces around the commas.


Narayan
 
Hi Narayan,

I do have a space after the comma. Your formula does drop the first term in proper column. It doesn't get the next terms in the same string. Is that because of the space?

Thanks,

Tony
 
Hi Narayan,

Is "&C$4&" the source terms?

Is "&$A5&" the header information?

The last C$4 is to instruct the formula to look at the next cell?


The second formula confused me since the cell information changed and I don't understand the rationale.


I appreciate the help.

Regards,

Tony
 
Hi Tony,


Can you please upload a sample file..

to upload please refer..

http://chandoo.org/forums/topic/posting-a-sample-workbook


BTW.. Please also look into below URL.

In below post.. Please change Delimeter from "_" to ","

http://chandoo.org/forums/topic/need-predefined-formula-for-text-to-column#post-32161


or In below post.. Please change all "." to ","

http://chandoo.org/forums/topic/help-breaking-data-within-a-cell-into-separate-components#post-43907


Please let us know.. If any of the above is matching with your requirement.


Regards,

Deb
 
Hi Tony ,


Sorry , my mistake , since I copy + pasted the formula from the wrong cell.


If you compare the two formulae from my two posts , they are :


1. =IF(ISNUMBER(SEARCH(","&B$1&",",","&$A2&",")),B$1,"")


2. =IF(ISNUMBER(SEARCH(", "&C$4&",",", "&$A5&",")),C$4,"")


If you adjust the second formula , it becomes :


=IF(ISNUMBER(SEARCH(", "&B$1&",",", "&$A2&",")),B$1,"")


The only difference is that in the first formula , I have considered that the input text comma separated text is something like :



Student,ID,Marks




After you mentioned that there will be a space after the commas , the input text should be :



Student, ID, Marks




This additional space character after the commas has been taken into account in the second formula.


Narayan
 
Hi Debraj and Narayan,

I'll work with the formula you've posted. In the meantime, here is a sample of what I'm trying to do.


https://dl.dropboxusercontent.com/u/73728520/Watchlist.xlsx


The first two lines show how I want the information displayed in the individual columns.


The lower rows show the information in a single cell and separated by a comma.


I really appreciate the help.

Thanks,

Tony
 
Hi Tony,


Try this..

Code: 
=IF(ISNUMBER(SEARCH(B$1,$A2)),B$1,"")


Drag Down and right..


https://dl.dropboxusercontent.com/u/78831150/Excel/Watchlist%20%28tonymjr%29.xlsx


Regards,

Deb
 
Hi Tony ,


The following formula was giving correct results !


=IF(ISNUMBER(SEARCH(", "&B$1&",",", "&$A2&",")),B$1,"")


it was just a matter of either increasing the column width , or selecting Left Aligned from the Alignment options !


Please note that this happens whenever you copy + paste a formula from this forum , instead of entering it manually. The resulting output is centered and the font is larger in size , with the result the cell appears empty.


Narayan
 
You must log in or register to reply here.
Back
Top