Remove specific character from field value

[karan_kn]

How to remove last string values from Field values (ex: ', Inc', ', inc', 'Inc.', 'inc.', 'Ltd.',  'Plc')

Field1:
Aero Incredible Work Out, Inc
Zero Impress, Ltd.

To change

Aero Incredible Work Out
Zero Impress

Thanks

[MayilVahanan]

HI Karan,

Try with SubField function, it will helps you.

=SubField(Field, ',',1)

=SubField('Aero Incredible Work Out, Inc', ',',1) gives Aero Incredible Work Out

[karan_kn]

Sorry, I'm getting only 'Aero', if I use the above condition since the 'Incredible' contains 'Inc', we need to remove the last 'Inc ' from the string 

[sunny_talwar]

I think @MayilVahanan solution is working

[MayilVahanan]

Hi Karan,

SubField(Field, ',',1)

SubField is the function helps to separate the words based on the delimiters like comma, space, or any other words.

Ex: Aero Incredible Work Out, Inc <- for this values, we need to fetch the values before comma separator. 

SubField(Field, ',',1) gives the expected results. 

Suppose, if you have more than 1 comma separator in your values, then use like below

=Left('Aero Incredible, Work, Out, Inc', Len('Aero Incredible, Work, Out, Inc')- Len(SubField('Aero Incredible, Work, Out, Inc', ',',SubStringCount( 'Aero Incredible, Work, Out, Inc',',')+1))-1)

[karan_kn]

Sorry If I confused both, I'm not looking to remove only for specific values, have thousands of values like this. I need a solution based on the field. need to eliminate Inc LLC from the field.

Company Name Field
Company Laboratories, Inc.
Company Oil, Inc
Company Oil, Inc.
Company Online, Inc.
Company Orthopaedic Clinic, LLC

New Company Name Field
Company Laboratories
Company Oil
Company Oil
Company Online
Company Orthopaedic Clinic

[Chanty4u]

Hi

can you try this?

a:
load * Inline [
"Company Name Field"
"Company Laboratories, Inc."
"Company Oil, Inc"
"Company Oil, Inc."
"Company Online, Inc."
"Company Orthopaedic Clinic, LLC "
];

result:
load *,
SubField("Company Name Field",',',-2) as new Resident a;
drop Table a;

exit SCRIPT;

[karan_kn]

It's removing text whatever after comma, I required to remove only 'Inc' or 'Inc.' or 'LLC' or ', Inc,' or ', Inc.'

[karan_kn]

Remove LLC, Inc from Company name, attached sample company

[QFabian · Accepted solution]

Hi, try this :

Load

mid(replace(replace(replace(upper([Company Name Field]), 'INC', '') , 'LTD', '') , 'PLC', ''), 1, len([Company Name Field] )-4)   as   [New Company Name Field]

Resident from