Forum Discussion

ThomasDay's avatar
ThomasDay
Impactful Individual
10 years ago
Solved

strip all Alpha chars from text string?

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 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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's avatar
ThomasDay
Impactful Individual
10 years ago

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

ThomasDay's avatar
ThomasDay
Impactful Individual
10 years ago
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.