Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
anushkavp27
Frequent Visitor

Extracting a particular text from column in a particular format

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.

4 REPLIES 4
ExcelMonke
Super User
Super User

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"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Greg_Deckler
Super User
Super User

@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
    )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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
Resolver III
Resolver III

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.