Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ThomasDay
Impactful Individual
Impactful Individual

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

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

ThomasDay
Impactful Individual
Impactful Individual

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.  

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.