Hi guys,my codes purpose is to shorten fields to make them only alphanumeric in a new column.
The following code does not work as a code because I do not know how to do shift+ctrl+enter in code.
Doing it with just the formula is fine and works fine with dragging down, but I want to use a macro so I can automate this throughout a directory.
I have tried .FormulaArray and get a error.
any help on making this a usable macro would be greatly appreciated. Thanks.
Code
Sub Indirect()
Range("F2:F5000").Formula = "=LEFT(E2, MIN(ROW(INDIRECT(""1:""&LEN(E2)))+(((CODE(MID(UPPER(E2), ROW(INDIRECT(""1:""&LEN(E2))), 1))>64)*(CODE(MID(UPPER(E2), ROW(INDIRECT(""1:""&LEN(E2))), 1))<91))+((CODE(MID(E2, ROW(INDIRECT(""1:""&LEN(E2))), 1))>47)*(CODE(MID(E2, ROW(INDIRECT(""1:""&LEN(E2))), 1))<58)))*1E+99)-1)"
End Sub
Display More