Forum Discussion

Modnar1's avatar
Modnar1
Copper Contributor
Feb 12, 2022

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

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 sta...
excel
Modnar1's avatar
Modnar1
Copper Contributor
Feb 12, 2022
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's avatar
mathetes
Gold Contributor
Feb 12, 2022

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.

 

Resources