TEXT Operator In Excel

Talk about anything at all....
Post Reply
thatengineer
Posts: 2
Joined: Wed Jun 06, 2018 11:09 pm

TEXT Operator In Excel

Post by 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.
OpenOffice 3.1 on Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: TEXT Operator In Excel

Post by 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

="TEMP" & TEXT(16; "000")
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
thatengineer
Posts: 2
Joined: Wed Jun 06, 2018 11:09 pm

Re: TEXT Operator In Excel

Post by 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.
OpenOffice 3.1 on Windows 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: TEXT Operator In Excel

Post by 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

="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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply