Forum Discussion
Impactful IndividualTesting if a string ONLY contains letters
I have a similar problem to this post that was in Excle, but need a solution in either Power Query or DAX
https://stackoverflow.com/questions/29855647/check-if-cell-contains-non-alpha-characters-in-excel
I have some heavily corrupted data for names and address fields that must ONLY contain letters. If they contain anything else i need to replace with a blank. So I guess this needs to test each charachter against a list "abcd......z" and if any don't match its FALSE
Suggestions appraciated
Mike
No worries.
I actually partially solved your problem with the code I posted above.
NumsToRemove = List.Transform({48..57 }, each Character.FromNumber(_))48 thru 57 corresponds to the https://www.asciitable.com/
48 = 0.
.
.
57 = 9
I left the other symbols for you to look up in the table. The link was provided as it was not my idea, I just modified it to a different need.
8 Replies
- ChrisMendoza
Resident Rockstar
Do you need to actually inspect the results or can a mass removal be done?
If the latter is all that is required then you can use the technique described in https://www.excelguru.ca/blog/2015/11/19/keep-only-numbers-in-power-query/.
My example dataset below.
Changing of course to: Edit - 48..57 is only nums 0 - 9; use the https://www.asciitable.com/ to include other symbols
NumsToRemove = List.Transform({48..57 }, each Character.FromNumber(_))Returns the following when using:
Text.Remove([Part Number],NumsToRemove)
It leaves the space so then just use:
Text.TrimStart([Result])
masplinHi Chris
Just need to wipe it if it isnt all letters as its garbagge data.
I'll check the link thanks
- masplin
The link is to keep only numbers os don't see how that helps as I need the opposite keep only text?
I could sort of use your power bi method except i woudl have to list every single charachter that isnt a letter. I have some charachters that are really weird so its actually impossible. i think I need ot do it the other way confrim that no charachter is not in the list abc...z? Is there a way to do that?
Mike