You can do this with a formula for column A, rather than VBA.

I would add two columns to store the data.

Formula for quantity: =IF(IFERROR(LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("KG",A2))+1)),"N/A")="N/A",IF(IFERROR(LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("L",A2))+1)),"N/A")="N/A",IF(IFERROR(LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("G",A2))+1)),"N/A")="N/A","Missing",LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("G",A2))+1))),LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("L",A2))+1))),LEFT(A2,LEN(A2)-(LEN(A2)-(FIND("KG",A2))+1)))

Formula for unit of measure: =IF(IFERROR(MID(A2,FIND("KG",A2),LEN(A2)-(LEN(A2)-(FIND("KG",A2))+1)),"N/A")="N/A",IF(IFERROR(MID(A2,FIND("L",A2),LEN(A2)-(LEN(A2)-(FIND("L",A2))+1)),"N/A")="N/A",IF(IFERROR(MID(A2,FIND("G",A2),LEN(A2)-(LEN(A2)-(FIND("G",A2))+1)),"N/A")="N/A","Missing",MID(A2,FIND("G",A2),LEN(A2)-(LEN(A2)-(FIND("G",A2))+1))),MID(A2,FIND("L",A2),LEN(A2)-(LEN(A2)-(FIND("L",A2))+1))),MID(A2,FIND("KG",A2),LEN(A2)-(LEN(A2)-(FIND("KG",A2))+1)))

Using the formulas will work better if you have other people using the file and don't want them having VBA issues, or if internal security prevents enabling VBA.

I'm not certain how to parse the column B text string using this formula though. That might require the VBA scripting option.