0

Let's make this straight.

I'm typing, and after leaving the cells there should be a text that will add at the beginning of whatever I typed.

is that possible? if so, then how?

I know about the CONCATENATE function but it wasn't my bet because deleting the data on a cells will also delete the function.

3 Answers 3

1

Natively, Excel does not support this.

It is usually done by creating 2 cells. One is the cell that you input text, the other is the cell with the outcome.

For example:

Cell A1 has the outcome, and Cell B1 has the input. Cell A1 will prepend the text "Answer: " to whatever is typed in cell B1, but only if Cell B1 contains text. The formula in Cell A1 would be:

=IF(B1="";"";CONCATENATE("Answer: ";B1))

If you really want to just use one cell, you will be required to use VBA macro's to add the after a cell changed. Do note, that if you go this route, you will also take into account that if a user edits a cell again, the added text is now in the cell too, and upon submitting, it is added again. So the macro has to see if the text was in there to begin with, and skip adding it a second time.

Usually it easier to create a small button near that cell that pops up an input field and calculate what the outcome would be based on that input.

That said, VBA scripting is very advanced, and unless you actually can provide code of your own, is considered off-topic here on SuperUser.

3
  • Alright then, I guess I have no choice but to use a script. Thanks.
    – Seynal
    Sep 2, 2017 at 7:16
  • Good luck with that. I'm a good programmer and even I would struggle with getting that to work properly. All the checks you have to do etc. I honestly don't think its worth the trouble.
    – LPChip
    Sep 2, 2017 at 9:26
  • this is the accepted answered, I already did it via vba script.
    – Seynal
    Sep 8, 2017 at 15:25
1

Select all cells to affect, then choose 'format cells' from context menu. In the 'numbers' tab choose 'user defined' and type '"My Text "@' into the definition field.

In case you want to type numbers you have to replace the @ with the number format you want to use. (e.g.: '"My Text "0' for rounded integers.)

You can also combine both formats: '"My Text "0;;;"My Text "@'

4
  • This will not work if the cell contains text instead of numbers.
    – LPChip
    Sep 2, 2017 at 11:31
  • In Libreoffice it works with numbers and text.
    – gnblizz
    Sep 2, 2017 at 21:36
  • 1
    Did you try '"My Text" @' ?
    – gnblizz
    Sep 2, 2017 at 21:50
  • In Microsoft Office it doesn't.
    – LPChip
    Sep 3, 2017 at 11:36
1

I'm not so sure if this will answer your question, but I just use the custom formatting in Excel. E.g. I want to see #001234 on the cell without having to type #00 in every single cell. So I just go to

numbers tab > custom format > create a custom format

and for my case I just created "#00"0 where the text inside the quotation marks define what is automatically added after I type in every cell.

1
  • 1
    Welcome to Super User. This is a great first-time answer! Even if it's not useful for the OP (for example, if they need the prefix because they are using the cell's value in a calculation elsewhere), it's a very useful answer for other future readers.
    – robinCTS
    Jun 13, 2018 at 4:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .