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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
remi
Advocate II
Advocate II

Return only 10 characters from a string of text under the format REQ00*****

Hello all, I need your help!

Want to extract 10 characters from a string of text- a[Column 2], and this word should always begin with REQ00***** (the stars are wildcards)

 

I've used the following formula:

Search = TRIM(LEFT(SUBSTITUTE(MID(a[Column 2],FIND("REQ00?????",a[Column 2],2,1),LEN(a[Column 2]))," ",REPT(" ",10)),10))

 

but I can't figure it order to start with REQ00, as it returns with ":", ":", "#" and it cuts from the entire length of 10 doing so, and I want to return just the reference.

 

Thank you and looking forward to your recommendation!

 

1 ACCEPTED SOLUTION

Try this:

 

MyText = var BeginText = SEARCH("REQ00",a[Column 1],1,BLANK())

RETURN IF(ISBLANK(BeginText),BLANK(), MID(a[Column 1], BeginText,10))

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

MyText = MID(a[Column 2], SEARCH("REQ00",a[Column 2]),10)

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

hey! Thank you Smoupre, for the very fast response!

 

The text in a[Column 2] has up to 255 characters so, unfortunately, the response I got for now is:

 

The search Text provided to function 'SEARCH' could not be found in the given text.

 

Looking forward to other suggestions. Thank you!

I don't understand what the number of characters has to do with it. SEARCH should return the position of the first occurrence of "REQ000" and then MID should start at that character and get 10 characters. Can you post some sample data? Is this all in a single table?


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Smoupre, 

 

Thank you for getting back to me!

The formula that I had returned 10 characters regardless if they were in the cells, having scenarios where it returns a totally different word (the first 10 characters of the cell), scenarios where if there was no space in front of word it will return the character/characters in front and  cut  down from my desired number e.g. REQ00456729 (this is one of the reasons why I've mentioned the string number - 255). As for the formula that you've helped me with it gives me an error form the start, with the text I've given you last time.

 

OK, I created the following enter data query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnINNDAwNDI2MVWK1YlWCsnILFYAokSFktSKEoXikqLMvHSFjNSiVAUcKovzc1MVcvNRFEA0l2QkloBUgHSDtSRiBwoYDBQmLnEF7CrQOUiuIskY3EbiVKYUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}})
in
    #"Changed Type"

I then created the following column in table "a":

 

MyText = MID(a[Column 1], SEARCH("REQ00",a[Column 1]),10)

This returns me the REQ numbers from the text. But, I may not be truly understanding what you are going for here.

 

 

 

 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Smoupre,

 

Sorry, I think that I'm doing something wrong, still getting an error...error on returning 10 caracters 20171208.PNG

 

The data is formatted as text in a[Column 1], but still getting the above message.

 

I'm trying to count unique REQ00 and maybe establish some sort of timeline.

 

Thank you mate.

Try this:

 

MyText = var BeginText = SEARCH("REQ00",a[Column 1],1,BLANK())

RETURN IF(ISBLANK(BeginText),BLANK(), MID(a[Column 1], BeginText,10))

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

thank you smoupre!

 

the formula is giving me exactly what needed. thx! you are awesome!

Glad we got there! 🙂


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
remi
Advocate II
Advocate II

Hello all, I need your help!

Want to extract 10 characters from a string of text- a[Column 2], and this word should always begin with REQ00***** (the stars are wildcards)

 

I've used the following formula:

Search = TRIM(LEFT(SUBSTITUTE(MID(a[Column 2],FIND("REQ00?????",a[Column 2],2,1),LEN(a[Column 2]))," ",REPT(" ",10)),10))

 

but I can't figure it order to start with REQ00, as it returns with ":", ":", "#" and it cuts from the entire length of 10 doing so, and I want to return just the reference.

 

Thank you and looking forward to your recommendation!

 

See reply to previous thread. Please try not to cross-post.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.