how to get rod od number

[KasiaB]

Hi There, I have a colum with text with numbers and numbers divided by "/" and "-". I just need text with numbers. What is the quickes way to get rid of numbers and special characters. Please advice. Thanks, Kasia 

[Anonymous · Accepted solution]

Hi @KasiaB ,

Have you tried @AlexisOlson ‘s solution?

I tried his code and got the results.

Sample data.

1.Add a custom column.

Table.SelectRows(#"Added Index",
    each [#"P/Date Shft"]<> null and
    not List.ContainsAll(Text.ToList([#"P/Date Shft"]), {"/", "-"})
)

2.Add as New Query.

3.Turn to table, expand the needed column and remove duplicates.

4.Merge as follows and then expand the column.

5.Fill down.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

In that case, just split the column with the delimiter as "-/". Even better is to use Text.BeforeDelimiter, with "/-" as the delimiter.

--Nate

[KasiaB]

Let me rephrase. I have an file with different production data that I need to transform. In one colum there are two types of data: machine name (text and number) and date (numbers that are separated by "/" and "-"). For all lines in this file I just need the machine name, so I would like to get rid of numbers separated by "/" and "-" and then I could fill down all line with machine name. So I don't want to get rid of only "/" and "-". 

Here is the screenshot from the file. Cell 1 is the name of machine that I need and line 2, 4, 6 is what I'm training to get rid of. Further down there are other names of machines. 

Hope that this time I made it more clear.

Thank!

[AlexisOlson]

Add a step to filter out the nulls. It will generate some code like this.

= Table.SelectRows(#"Changed Type", each ([#"P/Date Shift"] <> null))

Modify this code to also remove rows that contain both "/" and "-".

= Table.SelectRows(#"Changed Type",
    each [#"P/Date Shift"] <> null and
    not List.ContainsAll(Text.ToList([#"P/Date Shift"]), {"/", "-"})
)

[smpa01]

@KasiaB what is the desired output for the screenshot you gave ?

[KasiaB]

Machine names in all Cells down, so for this example would be "D Flexo10"

[Anonymous]

= Table.TransformColumns(TableOrPreviousStepName, {"Column Name", each Text.Remove(_,  {"/","-"}))

--Nate

[AlexisOlson]

Or you can specify what you want to keep instead of what to remove.

Table.TransformColumns(#"Prev Step", {"ColName", each Text.Select(_,  {"0".."9"}))

The above assumes you only want digits 0-9.

[smpa01]

@KasiaB  provide some sample string and desired output