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 

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