I wanna extract some specific values in a string For example, in the table below, i wanna automate the extraction of Unit of Measure and Pack Size based on the data on ITEM column.

Specifically, i want to automate the Pack Size column to fetch the numeric values after the non-numeric value from the right.

I have tried using RIGHT function for this instance. I somehow fetched and automate the values in the Unit of Measure correctly using right(cell, 2) but i need to do some data cleaning because single characters like G and L arent 2 characters.

CodePudding user response：

This formula works for your data set. It extracts everything after the last X in the Item and removes the Unit of Measure text as it is specified in the second column.

=SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"X","@",LEN(A2)-LEN(SUBSTITUTE(A2,"X",""))),1)),B2,"") 0

CodePudding user response：

With O365 you have the following approach in cell C1:

=LET(x, TEXTAFTER(A2:A5,"X", -1), size, TEXTSPLIT(x, {"ML","G","ML","L"}),
 unit, SUBSTITUTE(x, size, ""), VSTACK({"Unit of Measure","Pack Size"},
   HSTACK(unit, size)))

Here is the output, it generates the header, and extract the unit of measure and the pack size, spilling the entire output at once:

It assumes the information to find comes after the first X character in reverse order. If you want the pack size as numeric, then replace size inside HSTACK with: 1*size.