Ask Your Question

How to add text next to a number within the same cell in a spreadsheet without the number being affected

asked 2017-05-10 15:05:03 +0200

appreciatethehelp gravatar image

updated 2017-05-11 07:29:07 +0200

Hello. Concerning a spreadsheet that I have made; there are certain cells containing numbers that I want to add text next to within the same cell. However, when I do this, the numbers are no longer counted in the calculations made by the formulas used in the spreadsheet; they are, in essence, treated as text.

I have tried the method used to separate text from a formula contained within the same cell; adding an ampersand after the numbers, then using quotation marks around the text. This method, however, doesn't seem to work in this scenario.

Here is an example document of what I am trying to achieve; if someone could edit it to achieve the desired result explained in the document, then upload it, I could then review the changes they made to learn what needed to be done: C:\fakepath\Separate text from numbers example.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-05-10 15:30:27 +0200

JohnSUN gravatar image

updated 2017-05-11 18:09:59 +0200

You can do this in different ways, depending on whether the text is displayed and printed in the cell, or only serve as a commentary on the number.

In the first case, to make the necessary text in number format.

Text in cell

In the second case, use the formula with the function N() - the text will be visible in the formula bar, but will not change the number (N from the text is zero)

Text in formula

As for your example, here is one of many possible formatting options

Add text next to a number

It seems to me that you would have greatly simplified the task itself if simply put the word in the next column. Another way

A few examples of formatting cells see in this spreadsheet

edit flag offensive delete link more


if you want both the number and the text to be visible inside the cell, you could insert an invisible separator such as CHAR(2) between the number and the text. Then you could extract the number value with a formula:

=VALUE(LEFT(G1;FIND(CHAR(2); G1) - 1) )
librebel gravatar imagelibrebel ( 2017-05-10 15:54:29 +0200 )edit

@ JohnSUN Thanks for the reply, however am having trouble producing the desired result based on following what you have done in the image you left.

I have tried adding "This is a number "0.00" with text" as well as "This is a number "1.20" (which is the actual number I am using in the cell along with the text) with text" in the Format code section under format cells > numbers tab > number category, then selecting OK. After having done this, when I enter the text...

appreciatethehelp gravatar imageappreciatethehelp ( 2017-05-11 06:31:11 +0200 )edit

… in the cell (either before or after the number), nothing changes; the numbers in the cell are treated as text and are not counted in calculations. Also it is worth adding that I want the numbers displayed as currency too, with the dollar sign before the digits. I have edited my question; having added an example file of the problem. Perhaps the best way to see how you made the result I desire happen would be if you edited it then uploaded it; then I could examine the changes you made.

appreciatethehelp gravatar imageappreciatethehelp ( 2017-05-11 06:49:23 +0200 )edit

Your example is very facilitated understanding of your problem. I supplemented my answer.

JohnSUN gravatar imageJohnSUN ( 2017-05-11 08:05:39 +0200 )edit

Regarding your comment about using another "type" column to distinguish different types of food; I definitely agree with you; it would be easier. The reason I am enquiring about using text and numbers in the same cell is purely educational; I have wanted to know how to do it for awhile. That, and there have been a few instances in which it was a better option than adding another column for text.

How did you know what to put in the "format code" section to achieve the desired result?

appreciatethehelp gravatar imageappreciatethehelp ( 2017-05-11 15:09:16 +0200 )edit

Also, could you please attach the example document that you edited, so that I may download it for educational purposes?

Lastly, could you recommend any material for a beginner to read on this topic? I'm not sure whereabouts in the LO manual I would go to learn WHY the code you entered in the format code section works.

I don't really know any coding and am just learning as I go. Thank you for the generous donation of your time.

appreciatethehelp gravatar imageappreciatethehelp ( 2017-05-11 15:17:03 +0200 )edit

Most information about formatting cells I took from the Help.A file with examples of formatting I will add at the end of the main answer a little later

JohnSUN gravatar imageJohnSUN ( 2017-05-11 17:01:50 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-10 15:05:03 +0200

Seen: 5,203 times

Last updated: May 11 '17