Forum Discussion

Lawrence Ervin's avatar
Lawrence Ervin
Copper Contributor
Nov 17, 2017

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 (Other), Y (Work)

 

I need to take this data, eliminate all alpha then move it from here each into it's own columnar cell. I have manually extracted it but I'm looking for a faster solution. I've spoken to all three vendors and this is what none of them can give my but I need.

I appreciate any insight I can get.

Thanks a lot

Excel Online

9 Replies

  • DITUG_President's avatar
    DITUG_President
    Brass Contributor
    Nov 20, 2017

    Hello Lawrence,

    Based on your description I created a video of a non-code solution to your problem using flash fill and attached it below.

    If your data is exported as text or a csv file, there is another alternative solution if this does not work for you.

    I made some assumptions because you only provided 1 line (row) of data. So I created some additional rows. Take a look and let me know if this helps. I am using Excel 2016 version 1710.  I always recommend providing a few rows of sample data so it is easier to test a solution.

  • ksgoury's avatar
    ksgoury
    Copper Contributor
    Nov 18, 2017
    Hi I made a monthly planner on exceĺ but can not right the formula which can auto generate I want to write for eg 1/ 2/ 2017 3 ,2/2/2017 2 ,3/2/2017 m, 4/2/2017 1, 5/02/2017 0, and continues the same sequence the next.furthermore I want it to be associated to a person and same reproduced on another table
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Nov 17, 2017

    Lawrence,

     

    please clarify. What should the desired result look like?

     

    • Lawrence Ervin's avatar
      Lawrence Ervin
      Copper Contributor
      Nov 17, 2017

      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.

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Nov 18, 2017

        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"

Resources