Jan 03 2021 11:02 PM
Hello
I have a cell that contains multiple data separated by a comma in sheet one (Picture 1)
Then I have a table that has data for each of that data in sheet 2
I want to search the cell C2 that has multiple data in it through the table in sheet 2 and then write them the cell D2 separated by comma again.
Jan 03 2021 11:44 PM - edited Jan 03 2021 11:53 PM
SolutionOne way I believe you can do that, assuming you have the textjoin function (Ctrl+Shift+Enter after keying into the formula bar):
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH('لیست فیلترها'!$B$2:$B$19,C2)),'لیست فیلترها'!$C$2:$C$19,""))
But, I would think the new filter function would also work, if you have it. I personally don't, so this is not tested, but I would try:
=TEXTJOIN(", ",TRUE,FILTER('لیست فیلترها'!$C$2:$C$19,ISNUMBER(SEARCH('لیست فیلترها'!$B$2:$B$19,C2))))
There would be an issue in that you wouldn't get an error if there was an item in C2 that did not appear on the translation table (it would just get left out). So, you might consider adding a check column to confirm the number of items in the original and translated list are the same:
=LEN(C2)-LEN(SUBSTITUTE(C2,",",""))=LEN(D2)-LEN(SUBSTITUTE(D2,",",""))
Jan 04 2021 04:29 AM
@JMB17 Thank you so much it helps a lot
The only problem is that I want to make the range from 2 to 50 to have a space for future data adding.
In this case, it showed 0 for empty cells. can we do anything for this one as well?
I mean that in the formula I put the range from B2:B50, but actually I have data until B19 and the rest are empty for now. (In the future I will more data) so the formula showed 0 in the translate cell for each empty cell that is in the main cell.
I used
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH('لیست فیلترها'!$B$2:$B$50,C2)),'لیست فیلترها'!$C$2:$C$50,""))
actualy I have data until B19.
Jan 04 2021 07:00 AM
Jan 05 2021 01:49 AM
Jan 05 2021 04:33 PM
Jan 03 2021 11:44 PM - edited Jan 03 2021 11:53 PM
SolutionOne way I believe you can do that, assuming you have the textjoin function (Ctrl+Shift+Enter after keying into the formula bar):
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH('لیست فیلترها'!$B$2:$B$19,C2)),'لیست فیلترها'!$C$2:$C$19,""))
But, I would think the new filter function would also work, if you have it. I personally don't, so this is not tested, but I would try:
=TEXTJOIN(", ",TRUE,FILTER('لیست فیلترها'!$C$2:$C$19,ISNUMBER(SEARCH('لیست فیلترها'!$B$2:$B$19,C2))))
There would be an issue in that you wouldn't get an error if there was an item in C2 that did not appear on the translation table (it would just get left out). So, you might consider adding a check column to confirm the number of items in the original and translated list are the same:
=LEN(C2)-LEN(SUBSTITUTE(C2,",",""))=LEN(D2)-LEN(SUBSTITUTE(D2,",",""))