strip all Alpha chars from text string?

[ThomasDay]

Hello fellow DAX'ers,

This seems like it should be easy.  I have a column of text data coming into a zip code field.  It's public data meaning sometimes the data reads N/A or the name of a town etc 

ISTEXT() doesn't discriminate whether there are only numbers--it's a text column, so doesn't seem to help.

Is there a "wildcard" for TRIM or SUBStITUTE to mean any Alpha characters?  Or the opposite...Numeric characters.  Seems pretty useful.

Any ideas?

Tom

[Greg_Deckler · Accepted solution]

Messy, but you could do this:

NoText = Var NoAs = SUBSTITUTE([address],"a","")
Var NoBs = SUBSTITUTE(NoAs,"b","")
Var NoCs = SUBSTITUTE(NoBs,"c","")
Var NoDs = SUBSTITUTE(NoCs,"d","")
Var NoEs = SUBSTITUTE(NoDs,"e","")
Var NoFs = SUBSTITUTE(NoEs,"f","")
RETURN
NoFs

Rinse and repeat for all characters.

[ThomasDay]

Ah, that's great...I'm was just being lazy!!  Thanks, Tom

[ThomasDay]

NoText =   
                Var  TempVar =[TextStringFromLookup or some such thing]		
		Var NoAs = SUBSTITUTE(TempVar,"A","")
		Var NoBs = SUBSTITUTE(NoAs,"B","")
		Var NoCs = SUBSTITUTE(NoBs,"C","")
		Var NoDs = SUBSTITUTE(NoCs,"D","")
		Var NoEs = SUBSTITUTE(NoDs,"E","")
		Var NoFs = SUBSTITUTE(NoEs,"F","")
		Var NoGs = SUBSTITUTE(NoFs,"G","")
		Var NoHs = SUBSTITUTE(NoGs,"H","")
		Var NoIs = SUBSTITUTE(NoHs,"I","")
		Var NoJs = SUBSTITUTE(NoIs,"J","")
		Var NoKs = SUBSTITUTE(NoJs,"K","")
		Var NoLs = SUBSTITUTE(NoKs,"L","")
		Var NoMs = SUBSTITUTE(NoLs,"M","")
		Var NoNs = SUBSTITUTE(NoMs,"N","")
		Var NoOs = SUBSTITUTE(NoNs,"O","")
		Var NoPs = SUBSTITUTE(NoOs,"P","")
		Var NoQs = SUBSTITUTE(NoPs,"Q","")
		Var NoRs = SUBSTITUTE(NoQs,"R","")
		Var NoSs = SUBSTITUTE(NoRs,"S","")
		Var NoTs = SUBSTITUTE(NoSs,"T","")
		Var NoUs = SUBSTITUTE(NoTs,"U","")
		Var NoVs = SUBSTITUTE(NoUs,"V","")
		Var NoWs = SUBSTITUTE(NoVs,"W","")
		Var NoXs = SUBSTITUTE(NoWs,"X","")
		Var NoYs = SUBSTITUTE(NoXs,"Y","")
		Var NoZs = SUBSTITUTE(NoYs,"Z","")
		
		RETURN						
			Trim(NoZs)

Here's a cut and paste if anyone wants it.  

[Greg_Deckler]

Sorry man, you might have better luck in "M" in your Power Query, if I come across something better, I'll let you know.