# Extracting substring from within a string



## ChrisLane (Dec 26, 2022)

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 (Dec 26, 2022)

Give some examples of results.


----------



## mumps (Dec 26, 2022)

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 (Dec 26, 2022)

kvsrinivasamurthy said:


> Give some examples of results.



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


----------



## Sufiyan97 (Dec 26, 2022)

ChrisLane said:


> 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.


----------

