Forum Discussion
Need to use find and replace to change 1 digit in a 6 digit string of numbers
122812 Department:Women,Size:S,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122813 Department:Women,Size:M,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122814 Department:Women,Size:Large,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122815 Department:Women,Size:XL,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122816 Department:Women,Size:2XL,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122817 Department:Women,Size:3XL,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122811 Department:Women,Size:XS,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122812 Department:Women,Size:S,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122813 Department:Women,Size:M,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122814 Department:Women,Size:Large,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122815 Department:Women,Size:XL,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122816 Department:Women,Size:2XL,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
122817 Department:Women,Size:3XL,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,122811 new
222811 Department:Women,Size:XS,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,222811 new
222812 Department:Women,Size:S,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,222811 new
222813 Department:Women,Size:M,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,222811 new
222814 Department:Women,Size:Large,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,222811 new
222815 Department:Women,Size:XL,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,222811 new
222816 Department:Women,Size:2XL,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,222811 new
222817 Department:Women,Size:3XL,Size Type:Regular 105440 22.99 16.99 22.99 1000 Apparel,Womens,Scrubs,Top,XS,222811 new
Procedure: (assuming the text reaches you via file.txt
1.- Open Excel
2.- New Book
3.- Data Sheet
4.- Print TXT file data
5.- In the window that opens, choose delimiter -tab > tab
6.- Accept to Transform Data
7.- Open the advanced editor
8.- Paste the following text:
let
Origen = Csv.Document(File.Contents("C:\Users\USUARIO\Desktop\YourText.txt"),[Delimiter=" ", Columns=1, Encoding=1252]),
#"Dividir columna por delimitador" = Table.SplitColumn(Origen, "Column1", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
#"Dividir columna por posición" = Table.SplitColumn(#"Dividir columna por delimitador", "Column1.2", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.2.1", "Column1.2.2"}),
#"Dividir columna por posición1" = Table.SplitColumn(#"Dividir columna por posición", "Column1.1", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.1.1", "Column1.1.2"}),
#"Valor reemplazado" = Table.ReplaceValue(#"Dividir columna por posición1","1","2",Replacer.ReplaceValue,{"Column1.1.1", "Column1.2.1"}),
#"Columnas combinadas" = Table.CombineColumns(#"Valor reemplazado",{"Column1.1.1", "Column1.1.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Combinada"),
#"Columnas combinadas1" = Table.CombineColumns(#"Columnas combinadas",{"Column1.2.1", "Column1.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Combinada.1"),
#"Columnas combinadas2" = Table.CombineColumns(#"Columnas combinadas1",{"Combinada", "Combinada.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Combinada.2")
in
#"Columnas combinadas2"
9.- Accept changes (Done)
10 Close and Load in...
This loads the data back into Exce.
It is not a clean solution.
But in the consultation you can analyze the steps to learn how to do it.
Once done When they send you a new file, just run the query again and append the table to the old one.
The format of the columns will depend on what you need.
Best regards.