Thread: How to change cells in Selection to values

I changed the formula into value with the first codes below. This code, however, has some limitation. The value of the selected cells which starts with 0, for example, 0022111, will be turned into 22111 with this code. So I came up with the second one. What would you do if it were you?

With Range("D4", Range("D65536").End(xlUp)).Offset(0, 1) 
     
    .Formula = "=LEFT(RC[-1],FIND(""/"",RC[-1])-1)" 
    .Value = .Value 
End With

With Range("D4", Range("D65536").End(xlUp)).Offset(0, 1) 
     
    .Formula = "=LEFT(RC[-1],FIND(""/"",RC[-1])-1)" 
    .Copy 
End With

Selection.PasteSpecial Paste:=xlPasteValues

Maybe

With Range("D4", Range("D65536").End(xlUp)).Offset(0, 1)
     
    .Formula = "=LEFT(RC[-1],FIND(""/"",RC[-1])-1)"
    .Value = Format(.Value, "0000000")
End With

Originally Posted by royUK

Maybe

Thanks Roy but there is still limitation. The codes above can handle the example "0022111" I quoted but the real life case is there is no fix length for the value of every cells in selection. It could take six spaces as it did in my example but it could also take ten or even more.

Please don't quote whole posts. You didn't say that the the values were varying lengths