Extracting a particular text from column in a particular format

[anushkavp27]

Hi 

I have a column that descibes a part. 

It is of format: SSDR,480G,2A,IT276,27.8,EXAMP,LU

I am particularly interested in obtaining the size which is 480G.

The problem is that there are other variants in this:

Example: SSDR,3.8T,2A,IT27,8.9,X,99,AA (in thsi example, I want to extract 3.8T) 

SSDR,NMMVV,3.84T,X.2,EXAMPL(in this particualr example, the size i want = 3.84T is the 3rd parameter in the split string)

SSDR,1.6TB,NMMVV,X.2,XX1 (in this example, I want 1.6TB)

The other problem that arises is that this column doesn't have all values starting with SSDR.

We have values like this ARRY,ABC,ABCDE22,EXL,X99,T500 but since this does not start with SSDR, we can leave this blank in the new column we create.

[ExcelMonke]

If your intended result is always after the first "delimiter" (i.e. after the first comma) then you can consider using a combination of the LEFT and FIND functions. However, if it is randomly located, it might be difficult to write a logical formula unless you want to make it a bit more complex; e.g. something that will allow the logic to pull "return value after first delimiter and if value after delimiter is NMMVV"

[Greg_Deckler]

@anushkavp27 This will get you the cases you've specified except having the size in the 3rd parameter. If you have a list of codes you need to search for you could modify this to take that into account.

Column = 
    IF( 
        LEFT([Value], 5) <> "SSDR,",
        BLANK(),
            VAR __NextComma = FIND(",", [Value], 6, -1)
            VAR __Result = IF( __NextComma = -1, BLANK(), MID([Value], 6, __NextComma - 5 - 1) )
        RETURN
            __Result
    )

[anushkavp27]

Thank you for your response. I really appreciate it.

However, I also need to take care of the other cases, where the size is after the second comma in the string and they are a lot in number and hence, I have to account for them. And that is what makes this so complex 🙂 

[AUDISU]

Hi @anushkavp27 ,

We need a common factor to identify the size. As an example, Starting with a letter or end with a letter or always in a specific location.
Did you notice any common factor for size in your dataset?
Thanks