Need to use find and replace to change 1 digit in a 6 digit string of numbers

[Modnar1]

So my data has number formations such as "146179" where the first digit 1 indicates a specific meaning for ex that an item is new. I need to be able to use find replace to find all 6 digit groups starting with 1 and change to 2 while leaving the following 5 characters unchanged. Essentially I am copying large sets of data where the number is repeated in various sections to create New Used Other etc

[#2]

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

[#3]

@Modnar1 

"Essentially I am copying large sets of data where the number is repeated in various sections to create New Used Other etc"

That "etc" with which you end the sentence is troublesome in this sense: you clearly are NOT just doing one thing, such as replacing all "1" characters with "2"  

It's entirely possible that a formula could be used rather than Find...Replace (in fact, I'm skeptical about that approach, unless incorporated into a VBA or macro routine)  but it would be a lot easier to help if you were more complete in describing the task.

[#4]

In the data i posted you can see where the 6 digit strings are that i need to change though I realized that 1 of my fields has a 6 digit code I do not want included but that means i will have to do it by single columns which is fine. The etc is because the single digit needing changing could be ranging from 1-9 and need replacing with digits between 1-9.

[#5]

@Modnar1 

That is still not a complete description. A complete description would be along the lines of

• if there's a 1, replace with 2
• if 2 replace with 4
• if 3 replace with 2
• if 
• etc

Do you see the problem? You've given a good intro, but by no means enough to give you a full solution, no matter the method involved in that solution, whether formula, Find..Replace..or macro. You need to spell out in detail what the solution has to do with each condition it encounters.

By the way, are those fields with the six digits separate cells, or is that entire text part of a single cell?  And are the six digits in the form of text, even though numeric?

[#6]

@Modnar1 

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.

[#7]

@Modnar1 

Here's a single formula that will simply increment the first digit by one. So 1nnnnn becomes 2nnnnn, 2nnnnn becomes 3nnnnn and so forth. This uses the new LET function to make it more readable. You will need the newest version of Excel for this to work. It also assumes--which I think is the case--that this whole string is one. 

=LET(

idx,VALUE(LEFT(C3,1)),

NewLast,RIGHT(LEFT(C3,6),5),

NewFst,CHOOSE(idx,"2","3","4","5","6","7","8","9","1"),

NewFst&NewLast

)

It can be modified if those are not the replacements you desire (see below how NewFst works)

idx is an internal (to the formula) defined variable, containing the value of the first character

NewLast contains the last five digits of the first six digits

NewFst uses the CHOOSE function to pick, based on the value of idx, the text "2" or "3" etc. in sequence

The final formula concatenates the two text strings NewFst and NewLast to give your new six digit number.

I've attached a spreadsheet using this formula with some of the data from you, with the assumption, as noted above, that your data comes through as one solid text string.

[#8]

@mathetes 

I like it!
But, I understand both from the text and from the comment, that he wants to change the first digit of the ArticleID, from 1 to 2.
If you periodically receive a file with the information in that format, the ideal is to do it with PwerQuery.
Which allows you to automate the operation,
Best regards.

[#9]

I'm on a Mac and don't have Power Query, to my regrets. And I avoid macros if it's possible to write a formula. So I'm glad you liked it. Thanks!

I also think it's not JUST from 1 to 2, but as he (she?) said at another point, it could be any of the digits 1-9 being changed to another one of those digits. It represents a status code, and the need is more than just 1 to 2.

[#10]

@mathetes 

I like macros, the first ones I did in MsWorks, but for security they are a plague, since then I have wanted two things from spreadsheets:
1.- Be able to save variables with name << LET() >>
2.- Be able to Save Formulas with name << LAMBDA() >>
After more than 30 years, it is almost a reality.
Best regards.