The source system exports batch code 001A, while the receiving sheet expects 26. Convert it only after the source documentation confirms the code is hexadecimal.

Keep the original and convert the value beside it

Put 001A in A2 as text to keep the leading zeros. In B2, enter:

=IF(A2="","",IFERROR(DECIMAL(A2,16),"Check code"))

For this example, A represents ten and the preceding 1 is in the sixteens place. The result is 26.

Base-16 place values turn 001A into 16 plus 10, which is 26.

Illustrated calculation. Keep the source code as text.

Test a code that looks decimal and one that is invalid

0010 should give 16 and 00FF should give 255. 01G0 should say Check code, because G is outside the base-16 digit set. If the source supplies a prefix, normalize it according to that format in a separate column and keep the imported string for comparison.

0010 converts to 16, 00FF to 255 and 01G0 needs review.

Illustrated validation. Check one invalid digit before filling down.

Microsoft’s DECIMAL function reference