Forum Discussion

ThomasDay's avatar
ThomasDay
Icon for Impactful Individual rankImpactful 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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
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
Icon for Impactful Individual rankImpactful Individual
10 years ago

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

  • ThomasDay's avatar
    ThomasDay
    Icon for Impactful Individual rankImpactful 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.  

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

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