How can I use a cell formed by concatenation of a

问题:

I can add multi-line text after a formula in a cell. (see how to append a multi-line text after a formula in Microsoft Excel?) But now that cell had been turned into a text cell and it seems I cannot use the cell as a numeric cell, when I want the cell in a summation. how can I use a cell like

=CONCATENATE(F20/20, CHARACTER(10), "*note 4)")

as an input of a numeric calculation in another cell?

回答1:

The simplest solution would be just to reference F20 in your formula.

However, if that's not possible you can just reverse engineer your concatenate with a combination of LEFT() and FIND() to get the number back out of it to use in a calculation.

Let's say your concatenated cell is in A1, and returns:

9
(note 4)

Put this formula in another cell:

=LEFT(A1,FIND(CHAR(10),A1)-1)*1

And that will return 9, formatted as a number.

回答2:

I would go with what jeffreyweir offered. I would sum a different set of cells and put that sum under the cells that you have concatenated to have notes in them.

However, if you are trying to get the format and amount of that value and sum it in a formula without having the amount in two cells, you probably want to use formatting to get the effect rather than concatenation. Leave the number as a number instead of converting the number to being part of a text string. In other words instead of using:

=CONCATENATE(F20/20, CHAR(10), "*note 4)")

in the cell, just use:

=F20/20

Then, in the cell that you want the note to appear and also include in a sum, change the formatting to add the note.

To do this, select the cell, enter the Format Cells dialogue and select custom. For this particular result, you might choose a format like this (enter this in the box under "Type:"):

#,##0.00"*note 4)"

Then, before clicking "OK" to save it, go to the position between the quote and the asterisk and insert the line break character between the two characters by holding down the Alt key while you press 0010 on the numeric keypad. In other words, [Alt]+[0,0,1,0].

You will need to be careful of a couple of things. One is to ensure that the Wrap Text option is on for this cell and another is that the cell may need to be a bit wider than you would expect it to be or it will show # signs.

The attached shows the sum of three cells A1:A3 with A2 having the =F20/20 formula and F20 having the value 123. A2 has been set up with the formatting described above. Note that the sum formula is a normal formula as shown in the picture.