Removing variable amount of characters from front of cell

[Anonymous]

Hi all!

I'm working with badge numbers, and some of our badges start with either one letter, two letters, or none at all.

Joe Shmo	ZZ1234125
Derrick Dieter	Z23412344
Paul Piper	124244444
Aaron Iron	124321441
Bowlsey Bollsly	564234123

Is there any way that I can create something that recognizes the first few characters as letters and, if there aren't none, to leave the cell alone?

[negi007]

@Anonymous you can create a column like below in your data

Column =

var chr_1 = MID('Table'[badge ],1,1)*1

var chr_2 = MID('Table'[badge ],2,1)*1

var chr_3 = MID('Table'[badge ],1,2)*1

var ctr = ISERROR(chr_1)+ISERROR(chr_2)

return

MID('Table'[badge ],1,ctr)

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcwxC8JADIbhvxIyd0kudbd00ang1uOGIgEPoyfXivTf26bf8C4PfDHitSjcHq+CDY4jcRDiFlMTsdda8/0JfdZF686OQcR5mL4GQ/44EQvLPqfzVMsbLlsOCkwi5NSVn826QlfMZls3b09y/GJKfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Badge = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Num Only", each Text.Select([Badge], {"0".."9"}))
in
    #"Added Custom"

[Anonymous]

Thanks! Worked like a charm and I was able to link the id with another table in order to pull their names into a bigger dataset.

[negi007]

@Anonymous You can also try below way

Column =

var chr_1 = MID('Table'[badge ],1,1)*1

var chr_2 = MID('Table'[badge ],2,1)*1

var chr_3 = len('Table'[badge ])*1

var ctr = (ISERROR(chr_1)+ISERROR(chr_2))*1

return

//chr_3

//ctr

MID('Table'[badge ],ctr+1,chr_3-ctr)