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

[remi]

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!

[Greg_Deckler]

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

[remi]

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!

[Greg_Deckler]

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?

[remi]

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.

[Greg_Deckler]

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.

[remi]

Hi Smoupre,

Sorry, I think that I'm doing something wrong, still getting an error...

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.

[Greg_Deckler · 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))

[remi]

Hi,

thank you smoupre!

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

[Greg_Deckler]

Glad we got there! 🙂

[remi]

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!

[Greg_Deckler]

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