Forum Discussion
Need to use find and replace to change 1 digit in a 6 digit string of numbers
"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.
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.
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.- 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.
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?
