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

Question

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

mathetes · Answer

Modnar1&nbsp;&nbsp;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.&nbsp;&nbsp;=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&amp;NewLast)&nbsp;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 characterNewLast contains the last five digits of the first six digitsNewFst uses the CHOOSE function to pick, based on the value of idx, the text "2" or "3" etc. in sequenceThe final formula concatenates the two text strings NewFst and NewLast to give your new six digit number.&nbsp;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.&nbsp;

modnar1 · Answer

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

mathetes · Answer

Modnar1&nbsp;&nbsp;"Essentially I am copying large sets of data where the number is repeated in various sections to create New Used Other etc"&nbsp;That "etc" with which you end the&nbsp;sentence is troublesome in this sense: you clearly are NOT just doing one thing, such as replacing all "1" characters with "2" &nbsp;&nbsp;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) &nbsp;but it would be a lot easier to help if you were more complete in describing the task.

modnar1 · Answer

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.

mathetes · Answer

Modnar1&nbsp;&nbsp;That is still not a complete description. A complete description would be along the lines ofif there's a 1, replace with 2if 2 replace with 4if 3 replace with 2if&nbsp;etcDo 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.&nbsp;By the way, are those fields with the six digits separate cells, or is that entire text part of a single cell? &nbsp;And are the six digits in the form of text, even though numeric?

Forum Discussion

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

9 Replies

Resources