asked on Help to make a Boolean Search string from an Excel Row
Please see sample - I have a spreadsheet attained from a marketing database which I want to create a Boolean search per ROW for.
Sample.xlsx
As you can see - the sample data will have varying data in it and needs to have the format (From A2 example)
It needs to look like:
12345678 OR "Jed Bush" OR 088490033 OR jed@gmail.com OR "123 Fake St Springfield WA 12345 USA Earth Milky Way Galaxy Near Andromeda"
So names and addresses need to be concatenated and wrapped in quotes however contact numbers and email addresses need to be separated by an 'OR' statement
So this would look like:
876543213 OR "Barbara Valentine Bush" OR 095747773 OR 234890230 OR babs@gmail.com OR barbara@hotmail.com OR "321 Varsity Drive Wilmington 12345 VA"
Thanks for your help,
Sample.xlsx
As you can see - the sample data will have varying data in it and needs to have the format (From A2 example)
| ID | Name1 | Name2 | Name3 | Name4 | Name5 | Name6 | Name7 | Contact Number1 | Contact Number2 | Contact Number4 | Contact Number5 | Email1 | Email2 | Email3 | Email4 | Address1 | Address2 | Address3 | Address4 | Address5 | Address6 | Address7 | Address8 | Address9 | Address10 | Address11 | Address12 | Address13 | Address14 |
| 123245678 | Jed | Bush | 088490033 | jed@gmail.com | 123 Fake St | Springfield | 12345 | WA | USA | Earth | Milky | Way | Galaxy | Near | Andromeda |
It needs to look like:
12345678 OR "Jed Bush" OR 088490033 OR jed@gmail.com OR "123 Fake St Springfield WA 12345 USA Earth Milky Way Galaxy Near Andromeda"
So names and addresses need to be concatenated and wrapped in quotes however contact numbers and email addresses need to be separated by an 'OR' statement
| 876543213 | Barbara | Valentine | Bush | 095747773 | 234890230 | Babs@gmail.com | barbara@hotmail.com | 321 Varsity Drive | Wilmington | 12345 | VA |
So this would look like:
876543213 OR "Barbara Valentine Bush" OR 095747773 OR 234890230 OR babs@gmail.com OR barbara@hotmail.com OR "321 Varsity Drive Wilmington 12345 VA"
Thanks for your help,
* Excel TableMicrosoft Excel* booleanMicrosoft Office
Last Comment
Gary Benjamin
This isn't much prettier than yours but it's a start:
=TEXTJOIN(" OR ",TRUE,Table2[@ID],""""&CONCAT(Table2[@[Name1]:[Name7]])&"""",Table2[@[Contact Number1]],Table2[@[Contact Number2]],Table2[@[Contact Number4]],Table2[@[Contact Number5]],Table2[@Email1],Table2[@Email2],Table2[@Email3],Table2[@Email4],""""&CONCAT(Table2[@[Address1]:[Address14]])&"""")
Kevin
=TEXTJOIN(" OR ",TRUE,Table2[@ID],""""&CONCAT(Table2[@[Name1]:[Name7]])&"""",Table2[@[Contact Number1]],Table2[@[Contact Number2]],Table2[@[Contact Number4]],Table2[@[Contact Number5]],Table2[@Email1],Table2[@Email2],Table2[@Email3],Table2[@Email4],""""&CONCAT(Table2[@[Address1]:[Address14]])&"""")
Kevin
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
=A$2&C2&""" OR """&CONCAT(Table2[@[Name1]:[Name7]])&""" OR "&Table2[@[Contact Number1]]&" OR "&Table2[@[Contact Number2]]&" OR "&Table2[@[Contact Number4]]&" OR "&Table2[@[Contact Number5]]&" OR "&Table2[@Email1]&" OR "&Table2[@Email2]&" OR "&Table2[@Email3]&" OR "&Table2[@Email4]&""" OR """&CONCAT(Table2[@[Address1]:[Address14]])