A1 = 00001 … (1 formatted to 5 spaces = 00001)
Cell B1 =concat("Number ",A1)
Results in B1 = ‘Number 1’ … not 'number 00001’as I want.
How to copy leading zeros (0s) in this formula ?
Thanks, As always, you guys are AWESOME !
⌡im [THE BookMan]
Text functions like CONCAT, LEFT, RIGHT, MID, SUBSTITUTE etc. convert unformatted decimal cell values into strings. The string is character “1” when your A1 contains number 1. A formatting attribute must never have any influence on formula results.
Try: ="Number "&TEXT(A1;“00000”)
TEXT returns the referenced number as a numeric string according to the given format string.
Didn’t work … got a 501 error
Used =concat(“number 0000”,a1) … worked as I needed.
A bit of work, but worked.
(I don’t know why I didn’t think of this before I bothered you.
Thanks again
⌡im [THE BookMan]
This web application replaces straight quotes with typographic quotes 
… which is why you should never write ="Number "&TEXT(A1;“00000”), but ="Number "&TEXT(A1;"00000")
That is wrong: when you have 10 in A1, you will get a wrong number of digits.
Additionally, if you format your numbers with leading zeroes, that usually means that you have some identifiers, not numbers - and maybe you need to have a string in A1.
Indeed, any identifiers such as phone “numbers”, product IDs, zip codes etc. should be stored as text. They are just computer friendly names that happen to consist of digits. When you mix numeric and textual part numbers such as
1081598 (number)
1081598-A (text, variant of part No. 1081598)
you get several surprises when doing lookups, filtering, sorting because similar identifiers fall in completely different categories. When sorting mixed types of data, any text value is bigger than any number. All Text appears behind the numbers when sorted in ascending order. In a long list 1081598-A would be sorted far behind the number 1081598.
Likewise, number 123 is identical with the numbers formatted as 0123, 00123 or 000123 whereas the strings “123”, “0123”, 00123 and “000123” are different identifiers.
I would say, most of today’s spreadsheets don’t work reliably because most of today’s spreadsheets are poor database surrogates.
In a database one would store all these identifiers as text. The respective column’s type would be declared once before storing any data, and if these identifiers should consist of digits only, you could modify the user interface to accept digits only for this text column.
=Left(concat("number 0000";a1); 5)
but better use Villeroys suggestion and replace the curly quotes after pasting.