Forum Discussion
Find and replace?
Hi. I am a complete newbie to this forum and almost as green with excel. I am trying to resolve this issue in an excel import. 7049326328 L (Cell), 8286523017 L (Home), Y (Work), 7049326328 L (O...
Lawrence,
please clarify. What should the desired result look like?
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.
