Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Extracting text from strings in a single cell from a row of cells

Hi,


Is there an easier way to lookup a text / word in a string within row of cells and where a match is found return a header text - see example shown below.

User uploaded file

The table on the left contains the original compliance positions in groups by companies. I would like to populate the table on the right with compliance positions shown in the table on the left, using the header text (red box). Example 1 shows that "Company C" is non-compliant in left table and returns a "Non-Compliant" in the relevant cell for company C. The 2nd example Company's A, C, and Z are compliant in the left table and returns "Compliant" in the relevant cell for company's A, C and Z respectively. The same applies for the Partials.


The rows must also align as shown as the leftmost column will contain a unique reference (not shown in the example).


My current approach was to simply build a small matrix and use IF(COUNTIF($E5,"*Company A*"),"Compliant","") - This works fine, but is a little untidy - The resulting table shown above is really what I would like o achieve.


I also attempted to use nested "IFs", "Find", "Index" and "Match" combinations, based on the some early posts, but just could not get the syntax correct.


Would be grateful for alternative ways to the build the table on the right.


Thank you in advance ...


Pasel

MacBook Pro, Mac OS X (10.7.5)

Posted on Sep 15, 2016 10:18 AM

Reply
8 replies

Sep 15, 2016 10:55 AM in response to PASEL

This may work for you:

User uploaded file


A2=IF(COUNTA('Table 1 - Company Compliance Positions'::$A2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$A2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$A2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$A$1, ""), "")&IF(COUNTA('Table 1 - Company Compliance Positions'::$B2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$B2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$B2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$B$1, ""), "")&IF(COUNTA('Table 1 - Company Compliance Positions'::$C2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$C2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$C2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$C$1, ""), "")



this is shorthand for… select cell A2, then type (or copy and paste from here) the formula:

=IF(COUNTA('Table 1 - Company Compliance Positions'::$A2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$A2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$A2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$A$1, ""), "")&IF(COUNTA('Table 1 - Company Compliance Positions'::$B2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$B2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$B2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$B$1, ""), "")&IF(COUNTA('Table 1 - Company Compliance Positions'::$C2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$C2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$C2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$C$1, ""), "")




select cell A2, copy

select cell A2 thru the end of column F, paste

Sep 15, 2016 11:03 AM in response to PASEL

you can also use:

A2=IF(IFERROR(FIND(A$1,'Table 1 - Company Compliance Positions'::$A2), 0)>0, 'Table 1 - Company Compliance Positions'::$A$1, "")&IF(IFERROR(FIND(A$1,'Table 1 - Company Compliance Positions'::$B2), 0)>0, 'Table 1 - Company Compliance Positions'::$B$1, "")&IF(IFERROR(FIND(A$1,'Table 1 - Company Compliance Positions'::$C2), 0)>0, 'Table 1 - Company Compliance Positions'::$C$1, "")


fill like before

Sep 16, 2016 1:51 PM in response to PASEL

Hi Pasel,


Here is my take.

User uploaded file

A2= IF(IFERROR(FIND(A$1,Table 1::$A2,1),0)>0,Table 1::$A$1,"")&
IF(IFERROR(FIND(A$1,Table 1::$B2,1),0)>0,Table 1::$B$1,"")
&
IF(IFERROR(FIND(A$1,Table 1::$C2,1),0)>0,Table 1::$C$1,"")

I think this is also what Wayne was aiming for with his second solution.


I want to suggest briefer table names if it is workable for you. Less spaces will make it much easier to parse the formulas.


quinn

Extracting text from strings in a single cell from a row of cells

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.