Extracting substring from within a string

[ChrisLane]

Good afternoon

I have a string in a cell that contains a substring that I need to extract. The string is as follows"

AGTCode=00291323*CargoCarrier=ZZZ00028*Carrier=COS*MasterBillIssuedDate=2022-07-14 00:00:00.000*RadioCallSign=VRMN9*RL_NKMasterBillIssuedAt=CNNGB*VATClaimBackIndicator=Y

This string is the contents of a field within a record in an SQL Server table that I have extracted to Excel as part of a VIEW.

What I am trying to achieve is to extract the substring Carrier=COS.

The "Carrier" in the shipping line, and the "COS" is their customs carrier code.

The record can contain two fields: a job number, and the above, so I would like to extract two columns, the job number and the carrier code.

Complications? As you can see, "elements" in the string are encapsulated in asterisks - the Carrier Code element can be second in the list, or third, and the carrier code can consist of 3 or 4 characters.

If I was just using Excel, I could use the TEXT TO COLUMNS function, but not in this situation as I am compiling a report that needs the two columns mentioned above (job number, and carrier code).

I would appreciate any help I could get, please.

Many thanks

 

[kvsrinivasamurthy]

Give some examples of results.

 

[mumps]

There may be a more efficient way but this seems to work:
=MID(A1,FIND("*Carrier=",A1,1)+1,FIND("*",A1,40)-1-FIND("*Carrier=",A1,1))

 

[ChrisLane]

Give some examples of results.

Job No Carrier Code
==== ========
BCPTSI1004 MSC
SSIJNB00122 COS
SSIDUR001122 SAFM

 

[Sufiyan97]

Job No Carrier Code
==== ========
BCPTSI1004 MSC
SSIJNB00122 COS
SSIDUR001122 SAFM

I think not this way

First column row data and then in next column expected results.