How to string two numbers together with extra zeros

Question

How can I make a cell A1 "1234" with cell A2 "27" to make 12340027 as a number not text. In short, numbers in A2 are 1, 2 or 3 digits in length but they must be formatted to 4 digits e.g. 27= 0027, 4=0004. Using TEXT is resulting in lookup functions not working

Answer 1

Wrap your formula in VALUE() to turn the text back to a number:

=VALUE(A1 & TEXT(A2,"0000"))

Or multiply the text by one (or add zero):

=(A1 & TEXT(A2,"0000"))*1