If/Replace/Find - How to work it all into 1 formula


excel help. if last 7 numbers in any cell of column b match last 7 numbers in any cell of column a replace matching cell from column b with its matched cell in column a


111 thanks 5252527       111 you 5214785

111 hello 9874520           855 help 5252527

The 5252527 match. So the whole cell 855 would replace 111 thanks.

How do I translate this into a formula?




I somewhat struggled with how the question reads, but here's my attempt...  I would create some 'helper' columns (makes life a little easier, and I like to 'show my work' so it can be verified). I would 1) create Helper column (Column E in my file) of the last 7 digits in Column A and then next to it have a formula for the Original Column A (my Column F).  Once this is set up, you could perform a lookup on Column B last 7 characters. if it doesn't find a match, the results are N/A.  If it Does find a match, the results will be the 'Matched' cell that you want to replace it with.

Next I would mave the list filtered.  The values in Column G are what need to replace Column B.  You can do this in the filtered list, by making a formula in Column B to pull the results from the same row in Column G (Example in Col B3 the formula would be "=+G3"). You would then unfilter the list, Copy and replace Column B with it's own values (Paste Special as Values) and you would have updated cells.  See example

It may be easier to break down into smaller more manageable steps. Give it a try


Answer the Question

You must create an account to use the forum. Create an Account or Login