Forum Discussion
Find and Replace ### ### with just ###
What I am looking at is a column consisting of numbers with seven characters (###_L##, where "L" is a letter, # is a number, and "_" is just a space). However, there are sometimes numbers that are (#...
statejj16 Alternatively, and in case you prefer not to use VBA a simple, but perhaps not very elegant, formula comparing the first three characters with the last three can create the numbers how you want them. Then copy/paste as values the end result on top of the original column. With the numbers starting from in A1 that could be:
=IF(LEFT(A1,3)=RIGHT(A1,3),LEFT(A1,3),A1)and copy down.
If your Excel version supports dynamic arrays and the numbers are in A1:A100, try this:
=IF(LEFT(A1:A100,3)=RIGHT(A1:A100,3),LEFT(A1:A100,3),A1:A100)