Get value from text after delimiter

[jeongkim]

Hi,

I wanna extract first number 144272(after second -) and third number 19469(after fourth -).

Desired output is 114272-19469

Delimiter structure will be same but number string length are dynamic so trying to avoid Left Right function. 

[v-saisrao-msft]

Hi @jeongkim,

We haven’t heard back from you in a while. If your issue has been resolved, Mark the relevant response as the solution to help fellow community members.
If you still need support, let us know if you still need assistance.

Thank you.

[v-saisrao-msft]

Hi @jeongkim,

checking in to see if your issue has been resolved. If the response provided was helpful, kindly mark it as the solution so that others with the same issue can benefit.
let us know if you still need assistance.

Thank You.

[v-saisrao-msft]

Hi @jeongkim,

I hope you had a chance to review the solution shared earlier. If it addressed your question, Consider accepting it as the solution — it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you.

[danextian]

Hi @jeongkim 

Try this as a custom column in the query editor

let 
col = [Column1],
numbers = {"0".."9"}, 
text1 =  Text.Select( Text.BetweenDelimiters(col, "/", "/"), numbers),
text2 =  Text.Select( Text.BetweenDelimiters(col, "/", "/", 2, 0),numbers)
in
text1 & "-" & text2

[jeongkim]

Thanks this one seems easiest, can we do that in dax? 

[v-saisrao-msft · Accepted solution]

Hi @jeongkim,

Here is the DAX measure I used, which produced the desired output. Please review the attached PBIX file for your reference.

ExtractedCode = 
VAR FullText = [Column1]
VAR FirstSlash = SEARCH("/", FullText, 1)
VAR SecondSlash = SEARCH("/", FullText, FirstSlash + 1)
VAR ThirdSlash = SEARCH("/", FullText, SecondSlash + 1)

VAR Text1 = MID(FullText, FirstSlash + 1, SecondSlash - FirstSlash - 1)
VAR Text2 = MID(FullText, SecondSlash + 1, ThirdSlash - SecondSlash - 1)

VAR Digits = "0123456789"

VAR OnlyNums1 = CONCATENATEX(
    ADDCOLUMNS( 
        GENERATESERIES(1, LEN(Text1)), 
        "Char", MID(Text1, [Value], 1)
    ),
    IF(CONTAINSSTRING(Digits, [Char]), [Char], ""),
    ""
)

VAR OnlyNums2 = CONCATENATEX(
    ADDCOLUMNS( 
        GENERATESERIES(1, LEN(Text2)), 
        "Char", MID(Text2, [Value], 1)
    ),
    IF(CONTAINSSTRING(Digits, [Char]), [Char], ""),
    ""
)

RETURN OnlyNums1 & "-" & OnlyNums2

If this post helps, then please consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[HimanshuS-msft]

This was helpful.

[Ashish_Mathur]

Hi,

In Power Query, use Text between delimiter.  In that window, you can specify the delimiters you want to skip from left/right.

[ThxAlot]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvDx9dMFEfq+QU4hwbqGJiZG5kb6Pn4oHGdXH11DSxMzS30/J0//kHg3FxddA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
    Extracted = Table.AddColumn(Source, "Extracted", each let str=[String], nr=List.Select(Text.SplitAny(str, Text.Remove(str, {"0".."9"})), each _<>"") in (nr{0}? ?? "") & " - " & (nr{2}? ?? ""))
in
    Extracted

[Jai-Rathinavel]

Hi @jeongkim  Try the below M Query and see if it helps.

let
Source = Table.FromRows({{"PLMN-PLMN/MRBTS-144272/LNBTS-144272/LNCEL-19469/NBIOT_FDD-0"}}, {"Path"}),

// Split by "-"
SplitByDash = Table.AddColumn(Source, "Parts", each Text.Split([Path], "-")),

// Get 3rd and 7th elements
ExtractValues = Table.AddColumn(SplitByDash, "Result", each [Parts]{2} & "-" & [Parts]{6}),

Final = Table.SelectColumns(ExtractValues, {"Result"})
in
Final

[ajaybabuinturi]

Hi @jeongkim,
I would suggest to create a calculated column with below DAX logic to get desire out put. 

ExtractedValue = 
VAR FullText = 'TestTable'[Column1]
VAR FirstDash2 = SEARCH("-", FullText, SEARCH("-", FullText) + 1)
VAR SlashAfterSecond = SEARCH("/", FullText, FirstDash2)
VAR FirstNumber = MID(FullText, FirstDash2 + 1, SlashAfterSecond - FirstDash2 - 1)

VAR FourthDash = SEARCH("-", FullText, SEARCH("-", FullText, SEARCH("-", FullText, SEARCH("-", FullText) + 1) + 1) + 1)
VAR SlashAfterFourth = SEARCH("/", FullText, FourthDash)
VAR ThirdNumber = MID(FullText, FourthDash + 1, SlashAfterFourth - FourthDash - 1)

VAR Result = FirstNumber & "-" & ThirdNumber

RETURN
Result

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[jeongkim]

I've got error,,

actually I have first number(DU ID) from other column already so I used to use this. 

FDD Mapping =

    'LNCEL_FDD'[DU ID] & "-" &

        VAR _right = right([$dn], len([$dn]) - search("-", [$dn], 35, 0))

        RETURN LEFT(_right, SEARCH("/", _right) -1)

but the same error message pops up as below and I don't know why. 

[ajaybabuinturi]

@jeongkim Could you please provide some sample data of .pbix file, so that it is easy to understand your requirement and get the expected results.

Thanks,