Excel question

Is there a way/formula to convert these references?

Eg, I’d want -W1.01.01t+ to read -W2.01.01t-, so just the leading digit to change – but I’d need to apply this to thousands of cells

If the -W and t+ weren’t present, I’d just create a column with 1.00.00 and add the two together.

My Excel powers are limited though and the inclusion of letters has me stumped

Thanks

Can you just use SUBSTITUTE(A1,”W1.”,”W2.”,1)

Or just search/replace of course if you are just doing it once

If I understand it correctly, you should be able to just use global replace (CTRL + H) to replace all instances of W1.0 with W2.o. That’s assuming it’s just a one-off thing.

Can you just use SUBSTITUTE(A1,”W1.”,”W2.”,1)

Presuming A1 is a cell reference, I’ve pasted that formula into a cell and changed the source cell to the one I want and it does nothing – just stays as text. What does the 1 refer to at the end of your formula?

If I understand it correctly, you should be able to just use global replace (CTRL + H) to replace all instances of W1.0 with W2.o. That’s assuming it’s just a one-off thing.

This works – thank you 🙂

Presuming A1 is a cell reference, I’ve pasted that formula into a cell and changed the source cell to the one I want and it does nothing – just stays as text. What does the 1 refer to at the end of your formula?

I checked and it works fine for me but maybe I should have said what it does.  It will leave the original as text and the cell that you put the formula in will have a new copy of that text with the occurence of “W1.” replaced by “W2.”.   The ‘1’ just means replace the first occurence of W1 in the text, not all of them if your label had W1 twice

But the search and replace (Ctrl-H) is the better solution in this case so all done

Or use a combination of LEFT, RIGHT, MID, SEARCH, LEN etc to split the string down to its parts then rebuild with replacements for the bits you want to replace.

The problem with Find & Replace is that it can have effects you don’t want if your terms are too loose. You then don’t really have a trail if something on row 250,078 goes wrong.

Or do your find and replace, but do it on a copied column of your data, so you can go back if needs be.