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,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
=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]])