Removing characters before a semicolon and the space after that

[infock12]

Hi all,

A simple question please. I have a field that was formatted like the below.

U1234455; SURNAME, FIRSTNAME

U123455; SURNAME, FIRSTNAME

What I want is just the firstname, surname. Please note that some codes in the front have 7 numbers and some have 6 and some have only 5.

I would like to say exclude anything before the SURNAME, which includes the code, semicolon and the space after that.

I tried TRIM(LEFT(Name, 😎 etc., but they dont work accurately. Any help is appreciated.

Thanks,

Karthik

[rubenmarin · Accepted solution]

Hi Karthik, if the is only one '; ' you can use Subfield(fieldName, '; ', 2)

In case there can be more than one occurrences maybe is better using Mid(Index()):

Mid(FieldName, Index(FieldName, '; ')+2)

[trdandamudi]

Look at subfield() function and it will resolve your issue.

[its_anandrjs]

Try this way

LOAD

StringCol,

SubField(StringCol,',',-1) as FirstName,

Subfield( SubField(StringCol,';',2),',',1) as SurName

FROM

SData.xlsx

(ooxml, embedded labels, table is Sheet1);

Regards

Anand

[maxgro]

SIN:

load * inline [

field

U1234455; SURNAME, FIRSTNAME

U123455; SURNAME, FIRSTNAME

] (delimiter is '|');

Left Join (SIN)

load

  field,

  SubField(field, ';', -1) as SurnameAndFirstname,

  Subfield(SubField(field, ';', -1), ',', 1) as Surname,

  Subfield(SubField(field, ';', -1), ',', 2) as Firstname

Resident SIN;

[infock12]

Hi Ruben,

Thanks for the quick reply. The Subfield function worked great!!

Thanks,

Karthik

[infock12]

Thanks all for the responses, very helfpul.