TEXT Operator In Excel

Talk about anything at all....

TEXT Operator In Excel

Postby thatengineer » Wed Jun 06, 2018 11:14 pm

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
thatengineer
 
Posts: 2
Joined: Wed Jun 06, 2018 11:09 pm

Re: TEXT Operator In Excel

Postby FJCC » Wed Jun 06, 2018 11:38 pm

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")
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6714
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: TEXT Operator In Excel

Postby thatengineer » Thu Jun 07, 2018 12:21 am

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
thatengineer
 
Posts: 2
Joined: Wed Jun 06, 2018 11:09 pm

Re: TEXT Operator In Excel

Postby RusselB » Thu Jun 07, 2018 1:04 am

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.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4533
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: TEXT Operator In Excel

Postby MrProgrammer » Thu Jun 07, 2018 5:11 am

thatengineer wrote:Yes I use Excel 2013.
Then ask at an Excel forum, not here. A web search will find one.

thatengineer wrote: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.

thatengineer wrote: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")


thatengineer wrote: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.
Mr. Programmer
AOO 4.1.5 Build 9789 on Mac OS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3473
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to General Discussion

Who is online

Users browsing this forum: No registered users and 3 guests