I need to extract each University from the String field and place it in new column Institution. The University could be named in many different ways throughout all the records. Is this a job for fuzzy matching or something else? StringInstitutionUniversity of Limerick - Centre for Robotics & Intelligent Systems&ltUniversity of LimerickUniversity of Limerick - Synthesis & Solid State Pharmaceutical Centre (SSPC)<University of LimerickUniversity College Dublin - UCD School of Languages, Cultures & Linguistics<brUniversity College DublinUniversity of Leiden - Institute of Political Science of the Faculty of Social and Behavioural Sciences <University of LeidenDublin City University - School of Applied Language and Intercultural Studies<brDublin City UniversityUniversity of British Columbia<bUniversity of British ColumbiaNHTV Breda University of Applied Sciences - The Academy for Digital Entertainment<bNHTV Breda University of Applied SciencesWarwick Business School, The University of Warwick University of WarwickUniversity College Dublin - UCD School of Mathematics and Statistics<brUniversity College Dublin

Is this a job for Fuzzy Matching? I need to extract common values from a string.

I need to extract each University from the String field and place it in new column Institution.

The University could be named in many different ways throughout all the records.

Is this a job for fuzzy matching or something else?

String	Institution
University of Limerick - Centre for Robotics & Intelligent Systems&lt	University of Limerick
University of Limerick - Synthesis & Solid State Pharmaceutical Centre (SSPC)<	University of Limerick
University College Dublin - UCD School of Languages, Cultures & Linguistics<br	University College Dublin
University of Leiden - Institute of Political Science of the Faculty of Social and Behavioural Sciences <	University of Leiden
Dublin City University - School of Applied Language and Intercultural Studies<br	Dublin City University
University of British Columbia<b	University of British Columbia
NHTV Breda University of Applied Sciences - The Academy for Digital Entertainment<b	NHTV Breda University of Applied Sciences
Warwick Business School, The University of Warwick	University of Warwick
University College Dublin - UCD School of Mathematics and Statistics<br	University College Dublin

Transformation

Fuzzy Match

Preparation

Accepted answers

MarqueeCrew

Hey @NickC,

I do have an alternative to your wonderful formula:

(.*?)\s*[^a-z\s].*

It is a bit wordy, but here it goes:

(.*?) = Create a group of any characters up until the first time that you encounter whatever comes next.

\s* = 0 or more spaces followed by

[^a-z\s] = a non letter character or a non space

.* = followed by anything

We're default to case insensitive. Based upon the examples provided, this will work for you @jackdaniels. The extra benefit is that there will be no trailing spaces in the output.

Cheers,

Mark

All comments

NickC

Hello,

I have used a parsing tool to pull the relevant string from the text. The challenge is identifying the logic to break it out.

I used the RegEx tool with the Output Method set to Parse. Using the following Regular Expression (.*-|.*&|,.*), I'm sure somebody on the community can put together a shorter expression but this got me to the goal.

Please see completed workflow attached.

Thanks,

Nick

Reg Ex.yxmd

jackdaniels

Thanks NickC

Yes, identifying the logic is tricky. This should give me an idea to start with.

bharti_dalal

hi @jackdaniels,

Meanwhile learning Regex, you can also attain the results using text to column tools. I am attaching the solved workflow.

Regex.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703