Testing if a string ONLY contains letters

[masplin]

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

[ChrisMendoza]

@masplin,

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])

[masplin]

Hi 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

[ChrisMendoza]

Please read my statement above the first code block.

[masplin]

Sorry thought this was code for only keeping numbers. 

Where is says "So what does this do?  It actually creates a list of non-contiguous numbers (33-45, 47, 58-126), then transforms each value in the list into it’s alphanumeric equivalent.  A partial set of the results is shown here:"  Are these numbers to alphanumeric equivalents a standard thing that is listed somewhere?  I'm struggling to understand how List.transform({33..45  is related to the figure below as 33 seems to be "L".  Or is 33 the first number in list and 33="!"?

I can see if i could remove A..Z and a..z and be left with nothing that woudl be the right test

[ChrisMendoza · Accepted solution]

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.

[masplin]

Got it great thanks

[masplin]

Just for completeness here is my solution

let

CharsToRemove = List.Transform({33..44,46..47,58..64,123..255}, each Character.FromNumber(_)),

NumToRemove = List.Transform({48..57}, each Character.FromNumber(_)),

    Source = Csv.Document(File.Contents("U:\Dropbox\In-n-Out\Concorde\

Then this where I compare the length without special charachters with the length without either special or numbers. If they are the same I keep the version witohut special (which by defualt has no numbers) or it is blank. So this cleans up the random special charachters or fields just containing special charachters, and excludes any data containing numbers.

#"Added Custom2" = Table.AddColumn(#"Added Custom", "PEADDR3", each if Text.Length(Text.Remove([PEADDR3 RAW],CharsToRemove))= Text.Length(Text.Remove(Text.Remove([PEADDR3 RAW],CharsToRemove),NumToRemove)) then Text.Remove([PEADDR3 RAW],CharsToRemove) else null),

PEADDR£ RAW is my starting data and PEADDR3 is the clean data

Mike