Extract Specific Character Formats from a Text String

[Excel2021]

Hello,

I am trying to extract anything that comes after a hypen and has the following format character format "A123" from a Text String in power query (an excel formula would also work). Occasionally some of the formats I need to pull may vary and contain the format "AA123" or "AA123-AA".

String examples of each type are below in quotes and what I would like to pull is after the = on the right of the strings.

"GBS-1S-65-X555-7" = X555
"MRK-4S-95-XX555-7" = XX555
"KLS-5S-38-X555-UV" = X555-UV

Thanks in Advance.

 

[Excel2021]

Sorry that should have been hyphen above "-".

 

[GraH]

String examples of each type are below in quotes and what I would like to pull is after the = on the right of the strings.

"GBS-1S-65-X555-7" = X555
"MRK-4S-95-XX555-7" = XX555
"KLS-5S-38-X555-UV" = X555-UV

Sounds like

Text.AfterDelimiter(_, "=", {0, RelativePosition.FromEnd})

does the job.

 

[Excel2021]

Sounds like

Text.AfterDelimiter(_, "=", {0, RelativePosition.FromEnd})

does the job.

 

[Excel2021]

I tried the formula above but it did not seem to work for me. There is no "=" in the string. Only what is in the quotes would be an example of a typical string.

Thanks

 

[JEC]

How about

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.AddColumn(Source, "Custom", each
        let
            sp = Text.Split([Column1],"-"),
            txt = Text.AfterDelimiter([Column1], "-", {0 + Number.From(List.Count(sp) = 5), RelativePosition.FromEnd}),
            pos = Text.PositionOfAny(sp{List.Count(sp)-1},{"A".."Z"},Occurrence.All)
        in
            if not Text.Contains(txt,"-") or List.Count(pos) = 2 then txt else Text.Split(txt,"-"){0}
    )
in
    result

Book1
	A	B	C	D
1	Column1		Column1	Custom
2	GBS-1S-65-X555-7		GBS-1S-65-X555-7	X555
3	MRK-4S-95-XX555-7		MRK-4S-95-XX555-7	XX555
4	KLS-5S-38-X555-UV		KLS-5S-38-X555-UV	X555-UV
5	KLS-5S-38-X555		KLS-5S-38-X555	X555
Sheet1

 

[Excel2021]

How about

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.AddColumn(Source, "Custom", each
        let
            sp = Text.Split([Column1],"-"),
            txt = Text.AfterDelimiter([Column1], "-", {0 + Number.From(List.Count(sp) = 5), RelativePosition.FromEnd}),
            pos = Text.PositionOfAny(sp{List.Count(sp)-1},{"A".."Z"},Occurrence.All)
        in
            if not Text.Contains(txt,"-") or List.Count(pos) = 2 then txt else Text.Split(txt,"-"){0}
    )
in
    result

Book1
	A	B	C	D
1	Column1		Column1	Custom
2	GBS-1S-65-X555-7		GBS-1S-65-X555-7	X555
3	MRK-4S-95-XX555-7		MRK-4S-95-XX555-7	XX555
4	KLS-5S-38-X555-UV		KLS-5S-38-X555-UV	X555-UV
5	KLS-5S-38-X555		KLS-5S-38-X555	X555
Sheet1

This is great. It is definitely the closest I have been with this. On line 4 I just realized that sometimes the format also flips for some of the strings so it can also show up as KLS-5S-38-X555-UV or KLS-5S-38-UV-X555. Is possible to capture that also?

Thank you very much.

 

[JEC]

Maybe with one extra line

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.AddColumn(Source, "Custom", each
        let
            sp = Text.Split([Column1],"-"),
            txt = Text.AfterDelimiter([Column1], "-", {0 + Number.From(List.Count(sp) = 5), RelativePosition.FromEnd}),
            pos = Text.PositionOfAny(sp{List.Count(sp)-1},{"A".."Z"},Occurrence.All),
            pos2 = Text.PositionOfAny(sp{List.Count(sp)-2},{"A".."Z"},Occurrence.All)
        in
            if not Text.Contains(txt,"-") or List.Count(pos) = 2 or List.Count(pos2) = 2 then txt else Text.Split(txt,"-"){0}
    )
in
    result

 

[Excel2021]

Maybe with one extra line

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.AddColumn(Source, "Custom", each
        let
            sp = Text.Split([Column1],"-"),
            txt = Text.AfterDelimiter([Column1], "-", {0 + Number.From(List.Count(sp) = 5), RelativePosition.FromEnd}),
            pos = Text.PositionOfAny(sp{List.Count(sp)-1},{"A".."Z"},Occurrence.All),
            pos2 = Text.PositionOfAny(sp{List.Count(sp)-2},{"A".."Z"},Occurrence.All)
        in
            if not Text.Contains(txt,"-") or List.Count(pos) = 2 or List.Count(pos2) = 2 then txt else Text.Split(txt,"-"){0}
    )
in
    result

It doesn't seem to handle strings with "UV-" or "-UV" properly. If the string ends in "-UV" the rest of the string won't show up. Is there a way to split these from the string column and move them to their own column? That way your code could be applied to the leftover string and I can could concatenate the 2 UV columns back afterwards.

Thanks

 

[JEC]

Can you give multiple examples with desired outcomes? Then I can look again, maybe tomorrow