TEXT Operator In Excel

[thatengineer]

Why does =TEXT(16,"TEMP000") return a #VALUE error?
I am trying to get TEMP016 as the result.

I have tried it with other alphanumeric examples, like =TEXT(16, A092000) which should show A092016, and it works fine there.

[FJCC]

I see the error in OpenOffice Calc. Are you really using Excel?. I see your second example work, but changing the A to a D causes it to fail, though B and C work. Try

Code: Select all   Expand viewCollapse view

="TEMP" & TEXT(16; "000")

[thatengineer]

Yes I use Excel 2013.

The thing is that formula is part of a bigger formula containing LEFT, RIGHT operators.
I'm trying to get in sequence TEMP016A, TEMP017A, TEMP018A...

When seeing Calculation steps, all the formulas are working fine except this part. As mentioned, I've used the same formula on other alphanumerics and it worked fine.

[RusselB]

FJCC; The letter D causes it to fail due to the fact that D is a numeric format character (returns the day of the month in a date)
thatengineer: 1) Please note that this forum is for Apache Open Office and derivatives. Excel is not a derivative. Any suggestions made here may not work in Excel, even though they do work in Calc.
2) Do you have the numbers (15, 16, 17, 18, etc.) that will go in the first part of the TEXT function in a different column? If so, you can reference the cell that holds the number as the first parameter.
Eg:

Code: Select all   Expand viewCollapse view

="TEMP"&text(A2;"000")&"A"

Do to the fact that you state that this is part of a bigger formula, please post the formula you currently are working with, or (better yet) upload an example spreadsheet showing what you are starting with and what you want to end with.

[MrProgrammer]

Yes I use Excel 2013.

Then ask at an Excel forum, not here. A web search will find one.

Why does =TEXT(16,"TEMP000") return a #VALUE error?

It doesn't in Calc. It returns Err:502 (Invalid argument). TEMP000 is not a valid numeric format code in Calc.

I'm trying to get in sequence TEMP016A, TEMP017A, TEMP018A...

You can use these forumulas in Calc to get TEMP016A as the result. I don't know if they will work in Excel.
=TEXT(16;"""TEMP""000""A""")
=TEXT(16;"\T\E\M\P000\A")

TEXT Operator In Excel

TEXT is a function, not a operator.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.