Fixing field during LOAD XXXX Name ***NOT***

[Krzysztof-Wroclawski]

Hi Experts,

My regular format of the field is 123456 Big Customer Number One I checked Community forum to find the way to remove numbers. Solution which works for me is:

right ("Customer", len ("Customer"]) - 7) AS NameOnly,

However, I have another problem. Some of the Customer's names have ***NOT*** at the end. I would like to exclude number and ***NOT*** part from customer's name.

Summary:

Field   123456 The Biggest Corporation INC. should give The Biggest Corporation INC.

Field    654321 The Biggest Corporation INC. ***NOT*** should also give The Biggest Corporation INC.

Thank you in advance for all tips!

Kris

[OmarBenSalem]

do all the fields have the . ?

[Digvijay_Singh · Accepted solution]

You may try with replace() to remove the extra string -

replace(right ("Customer", len ("Customer"]) - 7),'***NOT***','') AS NameOnly,

[OmarBenSalem]

Do as follow:

load if(Index=0,NameOnly1, left(NameOnly1,Index-1)) as NameOnly;

load NameOnly1, index(NameOnly1,'***NOT***') as Index;

load Trim(PurgeChar("Customer",'0123456789')) as NameOnly1 Inline [

Customer

123456 The Biggest Corporation INC.

654321 The Biggest Corporation INC. ***NOT***

];

result:

[Krzysztof-Wroclawski]

Thank you, both! I used Digvijay solution, however, I see Omar's must work as well