Make cell text even if numbers

[jmh2008]

I have numbers, some with leading zero, and I want to format the cells to text so they will stay. When I select the cells and format them to Text, I see in the number formatting group of the ribbon, that they are text. The number go to the cell of the cell, which indicates Text, but if I do a calculation with to the of cells, ie =A1 + A2, it returns a number value and not an #value err. I just want to ensure that these numbers (Text Labels) do not accidentally get flipped to numbers. Because they are all different string length, I don't use a pre-determined custom number formatting with zeros. What is the best way to have numbers as "Labels". I also wanted to use them as named ranges, but named ranges do not like numbers either.

thanks.

 

[steve the fish]

Its ok they wont change to numbers again. Only the cell where the calculation is housed will change to number if it happens to be calculating text that looks like a number. Also numbers or text can be housed within a named range.

 

[Rick Rothstein]

I have numbers, some with leading zero, ....
I also wanted to use them as named ranges, but named ranges do not like numbers either.

The following was taken from here (note the first character requirement)... Define and use names in formulas - Excel

Learn about syntax rules for names

The following is a list of syntax rules that you need to be aware of when you create and edit names.

• Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Note You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

• Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.
• Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
• Name length A name can contain up to 255 characters.
• Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

 

[jmh2008]

Thanks for your reply, so another question for you. What is the best method or solution when importing data that has numbers as text and to have them stay as text and to have the leading zero recognized. (and not all the same length).