Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Using Search Function to find multiple words in a sentence

‎09-08-2021 02:59 AM

Hi,

 

I have an excel which has almost 4000 data columns. Each row a different text in it, and I need to find out certain words in these text and it should return that particular word to a column. Currently I am using the below formula :

 

 

Call Type Analysis = SWITCH (
    TRUE (),
    SEARCH ( "Misuse", 'CallReport'[Comment], 1, 0 ) > 0, "Misuse",
    SEARCH ( "Vandalism", CallReport[(No.) Cause], 1, 0 ) > 0, "Misuse",
    SEARCH ( "Misuse", CallReport[(No.) Cause], 1, 0 ) > 0, "Misuse",
    SEARCH ( "Rail", 'CallReport'[Comment], 1, 0 ) > 0, "Guide Rail",
    SEARCH ( "Light", 'CallReport'[Comment], 1, 0 ) > 0, "Light",
    SEARCH ( "Lights", 'CallReport'[Comment], 1, 0 ) > 0, "Light",
    "+Call Type Not Identified"
)

 

 

I have actually shortened the formula, it has almost 197 words written.

 

Could someone help me if we have any other process.

Is it possible that I can modify it because

 

If I write the program to find roller IN controller and it returns me roller, which I dont want to happen

Labels:
Message 1 of 3
1,233 Views
0
2 REPLIES 2
Anonymous
Not applicable

‎09-09-2021 02:50 AM

Hi,

I somewhat undertsood the concept in the link mentioned but not able to write the DAX or M, because I am beginner in it.

 

How can you help me in modyfying the query mentioned above. I will put in the whole formula I have written

 

Call Type Analysis = SWITCH (
TRUE (),
SEARCH ( "Vandalism", CallReport[(No.) Cause], 1, 0 ) > 0, "Misuse",
SEARCH ( "Misuse", CallReport[(No.) Cause], 1, 0 ) > 0, "Misuse",
SEARCH ( "Missuse", CallReport[(No.) Cause], 1, 0 ) > 0, "Misuse",
SEARCH ( "Misused", CallReport[(No.) Cause], 1, 0 ) > 0, "Misuse",
SEARCH ( "Rail", 'CallReport'[Comment], 1, 0 ) > 0, "Guide Rail",

SEARCH ( "Light", 'CallReport'[Comment], 1, 0 ) > 0, "Light",
SEARCH ( "Lights", 'CallReport'[Comment], 1, 0 ) > 0, "Light",
SEARCH ( "tubelight", 'CallReport'[Comment], 1, 0 ) > 0, "Light",
SEARCH ( "tube light", 'CallReport'[Comment], 1, 0 ) > 0, "Light",
SEARCH ( "lighting", 'CallReport'[Comment], 1, 0 ) > 0, "Light",
SEARCH ( "lightspot", 'CallReport'[Comment], 1, 0 ) > 0, "Light",
SEARCH ( "spotlight", 'CallReport'[Comment], 1, 0 ) > 0, "Light",
SEARCH ( "tube", 'CallReport'[Comment], 1, 0 ) > 0, "Light",

SEARCH ( "Fan", 'CallReport'[Comment], 1, 0 ) > 0, "Fan",
SEARCH ( "Cabin Fan", 'CallReport'[Comment], 1, 0 ) > 0, "Fan",

SEARCH ( "Door", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "lock", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "landing lock", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "landing contact", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "lockset", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "Doors", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "Door Spring", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "Door Shoe", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "Door Roller", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "Pickup Roller", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "Pickup", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "Hanger Roller", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "Hanger", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "Coupler", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "male", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "female", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "aircod", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "guide shoes", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "guide shoe", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "spring", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "sill", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "track", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "cuppler", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "curtain", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "eccentric", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "encentric", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "photocell", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "photo cell", 'CallReport'[Comment], 1, 0 ) > 0, "Door",
SEARCH ( "track", 'CallReport'[Comment], 1, 0 ) > 0, "Door",

SEARCH ( "assistance", 'CallReport'[Comment], 1, 0 ) > 0, "Assistance",
SEARCH ( "Cleaning", 'CallReport'[Comment], 1, 0 ) > 0, "Cleaning",

SEARCH ( "Brake", 'CallReport'[Comment], 1, 0 ) > 0, "Brake",
SEARCH ( "Brakes", 'CallReport'[Comment], 1, 0 ) > 0, "Brake",

SEARCH ( "No Fault", 'CallReport'[Comment], 1, 0 ) > 0, "No Fault",
SEARCH ( "Contactor",'CallReport'[Comment], 1, 0 ) > 0, "Contactor",
SEARCH ( "Contacter",'CallReport'[Comment], 1, 0 ) > 0, "Contactor",
SEARCH ( "Contactors",'CallReport'[Comment], 1, 0 ) > 0, "Contactor",
SEARCH ( "Relay",'CallReport'[Comment], 1, 0 ) > 0, "Contactor",
SEARCH ( "HDIF",'CallReport'[Comment], 1, 0 ) > 0, "Contactor",
SEARCH ( "LADN",'CallReport'[Comment], 1, 0 ) > 0, "Contactor",
SEARCH ( "Auxillary",'CallReport'[Comment], 1, 0 ) > 0, "Contactor",
SEARCH ( "Axillary",'CallReport'[Comment], 1, 0 ) > 0, "Contactor",
SEARCH ( "Axilary",'CallReport'[Comment], 1, 0 ) > 0, "Contactor",

SEARCH ( "Communication",'CallReport'[Comment], 1, 0 ) > 0, "Communication",
SEARCH ( "CAN BUS",'CallReport'[Comment], 1, 0 ) > 0, "Communication",
SEARCH ( "CANBUS",'CallReport'[Comment], 1, 0 ) > 0, "Communication",

SEARCH ( "PCB", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",

SEARCH ( "Intercom", 'CallReport'[Comment], 1, 0 ) > 0, "Intercom", 

SEARCH ( "Fire", 'CallReport'[Comment], 1, 0 ) > 0, "Fire",
SEARCH ( "False", 'CallReport'[Comment], 1, 0 ) > 0, "False",
SEARCH ( "Water", 'CallReport'[Comment], 1, 0 ) > 0, "Water",
SEARCH ( "Rain", 'CallReport'[Comment], 1, 0 ) > 0, "Rain",

SEARCH ( "Foreign", 'CallReport'[Comment], 1, 0 ) > 0, "Foreign Object",
SEARCH ( "Object", 'CallReport'[Comment], 1, 0 ) > 0, "Foreign Object",
SEARCH ( "Objects", 'CallReport'[Comment], 1, 0 ) > 0, "Foreign Object",
SEARCH ( "Forign", 'CallReport'[Comment], 1, 0 ) > 0, "Foreign Object",

SEARCH ( "Button", 'CallReport'[Comment], 1, 0 ) > 0, "Button",
SEARCH ( "Buttons", 'CallReport'[Comment], 1, 0 ) > 0, "Button",
SEARCH ( "Butons", 'CallReport'[Comment], 1, 0 ) > 0, "Button",
SEARCH ( "Buton", 'CallReport'[Comment], 1, 0 ) > 0, "Button",
SEARCH ( "Bush", 'CallReport'[Comment], 1, 0 ) > 0, "Button",

SEARCH ( "Sensor", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",
SEARCH ( "Sensors", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",
SEARCH ( "Switch", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",
SEARCH ( "Pencil", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",
SEARCH ( "Bistable", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",
SEARCH ( "ETSD", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",
SEARCH ( "Magnet", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",
SEARCH ( "Proximity", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",
SEARCH ( "sencer", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",
SEARCH ( "Vane", 'CallReport'[Comment], 1, 0 ) > 0, "Sensor/Switch",

SEARCH ( "IGBT", 'CallReport'[Comment], 1, 0 ) > 0, "Controller",

SEARCH ( "Chain", 'CallReport'[Comment], 1, 0 ) > 0, "Chain",
SEARCH ( "Handrail", 'CallReport'[Comment], 1, 0 ) > 0, "Handrail",

SEARCH ( "ARD", 'CallReport'[Comment], 1, 0 ) > 0, "ARD/MRD/Battery",
SEARCH ( "MRD", 'CallReport'[Comment], 1, 0 ) > 0, "ARD/MRD/Battery",
SEARCH ( "UPS", 'CallReport'[Comment], 1, 0 ) > 0, "ARD/MRD/Battery",
SEARCH ( "Battery", 'CallReport'[Comment], 1, 0 ) > 0, "ARD/MRD/Battery",

SEARCH ( "*0", 'CallReport'[Comment], 1, 0 ) > 0, "Error 0",
SEARCH ( "Noise", 'CallReport'[Comment], 1, 0 ) > 0, "Noise",
SEARCH ( "Speed Monitor", 'CallReport'[Comment], 1, 0 ) > 0, "Speed Monitor",
SEARCH ( "ELCB", 'CallReport'[Comment], 1, 0 ) > 0, "ELCB",

SEARCH ( "Drive", 'CallReport'[Comment], 1, 0 ) > 0, "Drive",
SEARCH ( "Vacon", 'CallReport'[Comment], 1, 0 ) > 0, "Drive",

SEARCH ( "Encoder", 'CallReport'[Comment], 1, 0 ) > 0, "Encoder",
SEARCH ( "Governor", 'CallReport'[Comment], 1, 0 ) > 0, "Governor",
SEARCH ( "Tension", 'CallReport'[Comment], 1, 0 ) > 0, "Tension Pulley",
SEARCH ( "Rope", 'CallReport'[Comment], 1, 0 ) > 0, "Rope",
SEARCH ( "TMI", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "IOC", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "CPU", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "CPUA", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "UCC", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "UCM", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "UCM", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "CMBK", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "Interface", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "Motherboard", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "Mother", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "UCP", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "UCP+", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",
SEARCH ( "PCB", 'CallReport'[Comment], 1, 0 ) > 0, "PCB",

SEARCH ( "Bearing", 'CallReport'[Comment], 1, 0 ) > 0, "Mechanical",
SEARCH ( "Diverter", 'CallReport'[Comment], 1, 0 ) > 0, "Mechanical",
SEARCH ( "Sheave", 'CallReport'[Comment], 1, 0 ) > 0, "Mechanical",
SEARCH ( "Counterweight", 'CallReport'[Comment], 1, 0 ) > 0, "Mechanical",
SEARCH ( "Compensation", 'CallReport'[Comment], 1, 0 ) > 0, "Mechanical",

SEARCH ( "Inlet", 'CallReport'[Comment], 1, 0 ) > 0, "Inlet",
SEARCH ( "Motor Speed Error", 'CallReport'[Comment], 1, 0 ) > 0, "Motor Speed Error",

SEARCH ( "Display", 'CallReport'[Comment], 1, 0 ) > 0, "Display",
SEARCH ( "Video", 'CallReport'[Comment], 1, 0 ) > 0, "Display",
SEARCH ( "LCD", 'CallReport'[Comment], 1, 0 ) > 0, "Display",

SEARCH ( "A/C", 'CallReport'[Comment], 1, 0 ) > 0, "Temperature",
SEARCH ( "Conditioner", 'CallReport'[Comment], 1, 0 ) > 0, "Temperature",
SEARCH ( "Air Conditioner", 'CallReport'[Comment], 1, 0 ) > 0, "Temperature",
SEARCH ( "temperature", 'CallReport'[Comment], 1, 0 ) > 0, "Temperature",
SEARCH ( "Humidity",'CallReport'[Comment], 1, 0 ) > 0, "Temperature",
SEARCH ( "moisture",'CallReport'[Comment], 1, 0 ) > 0, "Temperature",
SEARCH ( "water",'CallReport'[Comment], 1, 0 ) > 0, "Temperature",
SEARCH ( "mist",'CallReport'[Comment], 1, 0 ) > 0, "Temperature",
SEARCH ( "cooling", 'CallReport'[Comment], 1, 0 ) > 0, "Temperature",

SEARCH ( "Power Supply",'CallReport'[Comment], 1, 0 ) > 0, "Power Supply",
SEARCH ( "ELCB",'CallReport'[Comment], 1, 0 ) > 0, "Power Supply",
SEARCH ( "Mainswitch",'CallReport'[Comment], 1, 0 ) > 0, "Power Supply",
SEARCH ( "Main Supply",'CallReport'[Comment], 1, 0 ) > 0, "Power Supply",
SEARCH ( "Main switch",'CallReport'[Comment], 1, 0 ) > 0, "Power Supply",
SEARCH ( "Phase",'CallReport'[Comment], 1, 0 ) > 0, "Power Supply",
SEARCH ( "SMPS",'CallReport'[Comment], 1, 0 ) > 0, "Power Supply",
SEARCH ( "SMBS",'CallReport'[Comment], 1, 0 ) > 0, "Power Supply",

SEARCH ( "DSC",'CallReport'[Comment], 1, 0 ) > 0, "DSC",

SEARCH ( "Independent",'CallReport'[Comment], 1, 0 ) > 0, "Priority Key",
SEARCH ( "Priority",'CallReport'[Comment], 1, 0 ) > 0, "Priority Key",

SEARCH ( "Dust",'CallReport'[Comment], 1, 0 ) > 0, "Dust",
SEARCH ( "Dirt",'CallReport'[Comment], 1, 0 ) > 0, "Dust",
SEARCH ( "Mud",'CallReport'[Comment], 1, 0 ) > 0, "Dust",
SEARCH ( "Stone",'CallReport'[Comment], 1, 0 ) > 0, "Dust",

SEARCH ( "Brake",'CallReport'[Comment], 1, 0 ) > 0, "Brake",
SEARCH ( "Brakes",'CallReport'[Comment], 1, 0 ) > 0, "Brake",

SEARCH ( "Camera",'CallReport'[Comment], 1, 0 ) > 0, "Camera",
SEARCH ( "CCTV",'CallReport'[Comment], 1, 0 ) > 0, "Camera",
SEARCH ( "Intercom",'CallReport'[Comment], 1, 0 ) > 0, "Intercom",
SEARCH ( "Interphone",'CallReport'[Comment], 1, 0 ) > 0, "Intercom",

SEARCH ( "Valve", 'CallReport'[Comment], 1, 0 ) > 0, "Valve",
SEARCH ( "Value", 'CallReport'[Comment], 1, 0 ) > 0, "Valve",
SEARCH ( "rupture", 'CallReport'[Comment], 1, 0 ) > 0, "Valve",
SEARCH ( "bucher", 'CallReport'[Comment], 1, 0 ) > 0, "Valve",
SEARCH ( "hydraulic", 'CallReport'[Comment], 1, 0 ) > 0, "Valve",
SEARCH ( "hydraulic", 'CallReport'[Comment], 1, 0 ) > 0, "Valve",
SEARCH ( "hydrolic", 'CallReport'[Comment], 1, 0 ) > 0, "Valve",

SEARCH ( "Comb", 'CallReport'[Comment], 1, 0 ) > 0, "Comb/Demarcation",
SEARCH ( "Demarcation", 'CallReport'[Comment], 1, 0 ) > 0, "Comb/Demarcation",

SEARCH ( "PLC", 'CallReport'[Comment], 1, 0 ) > 0, "PLC",
SEARCH ( "TUV", CallReport[(No.) Cause], 1, 0 ) > 0, "Inspection",
SEARCH ( "third", CallReport[(No.) Cause], 1, 0 ) > 0, "Inspection",
SEARCH ( "Inspection", CallReport[(No.) Cause], 1, 0 ) > 0, "Inspection",
SEARCH ( "Intercom", CallReport[(No.) Cause], 1, 0 ) > 0, "Intercom",
SEARCH ( "Brake", CallReport[(No.) Cause], 1, 0 ) > 0, "Brake",
SEARCH ( "Buttons", CallReport[(No.) Fault], 1, 0 ) > 0, "Button",
SEARCH ( "Brake", CallReport[(No.) Fault], 1, 0 ) > 0, "Button",
SEARCH ( "Lighting", CallReport[(No.) Fault], 1, 0 ) > 0, "Light",
SEARCH ( "Doors", CallReport[(No.) Fault], 1, 0 ) > 0, "Door",
SEARCH ( "Door", CallReport[(No.) Fault], 1, 0 ) > 0, "Door",
SEARCH ( "Temperature", CallReport[(No.) Fault], 1, 0 ) > 0, "Temperature",
"+Call Type Not Identified"
)

 

Message 3 of 3
1,103 Views
0
AllisonKennedy
Super User
Super User

‎09-08-2021 04:22 AM

@Anonymous  are you assuming words are separated by space? You could either add a space before roller, ie search for " roller " (which won't help if it's the first or last word) or see if Imke's post helps: 

https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/

 

This is using Power Query to do the transformation, and also allows you to create a mapping table, rather than needing to keep the 197 words in your DAX formula. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Message 2 of 3
1,167 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All