Forum Discussion
Find and replace?
Certainly, thank you for your inquiry.
As you can tell these are multiple phone numbers per client. I actually need to eliminate the alpha and have each number in it's own cell in one column. When I have the data in this format it has already been imported into my CRM and this is the export data.
Lawrence,
since you didn't show what the desired output should look like I give you my interpretation.
The code is in M (Power Query).
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source," (Cell)","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," (Home)","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," (Work)","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2"," (Other)","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3"," L","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4"," Y","",Replacer.ReplaceText,{"Column1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value5", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type number}, {"Column1.2", type number}, {"Column1.3", type text}, {"Column1.4", type number}, {"Column1.5", type text}})
in
#"Changed Type"
Thanks a lot. That looks a little daunting but I'm going to give it a try. I'll reach back out if I have questions.
I really appreciate the time you spent working on that.
Thanks again.
Detlef,
That's mainly my exercise to make the script more dynamic (remove all non-digits plus split on columns dynamically depends on how many numbers we have). Require some coding, nonetheless
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // Remove from the text all non digits which are in the list // and extra comma at the end of the text if exists RemoveNotDigits = Table.AddColumn(Source, "Custom", each Text.TrimEnd( Text.Remove([Column1], List.Combine({{"A".."z"},{" ","(", ")"}})), ",") ), RemoveDoubleCommas = Table.ReplaceValue(RemoveNotDigits,",,",",",Replacer.ReplaceText,{"Custom"}), // next block is to split on columns dynamically // Temporary column to calculate number of commas in each string Splitters = Table.AddColumn(RemoveDoubleCommas, "Splitters", each List.Count(Text.Split([Custom],","))), // and max number of splitters in the table maxColumns = List.Max(Splitters[Splitters]), // generate the list with names of columns ListOfColumns = List.Transform({1..maxColumns}, each "Number " & Text.From(_)), SplitNumbers = Table.SplitColumn(Splitters, "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ListOfColumns), RemoveTemporary = Table.RemoveColumns(SplitNumbers,{"Column1", "Splitters"}) in RemoveTemporaryIMHO, same could be done by formulas, e.g. by set of nested SUBSTITUTE for entire alphabet and split by delimiter the then.
Sergei,
uh, that is still above my abilities.
But I would get rid of the "RemoveDoubleCommas"-step because I assume that there are 5 fields with phone numbers and some are empty.
Detlef,
That's not a rocket since, few exercises and with your skills you'll be here.
Extra comma is most probably the misprint, on the other hand such step costs practically nothing. More we check on such kind of errors the better - life becomes bit easier on maintenance phase.
