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 

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'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.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community 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
      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.