Excel auto populate cell

Question

I have a spreadsheet with 19k names. In column B, a code is entered for each row(name). All codes begin with R. Is there a way to pre populate each cell with an R so the user can just enter the rest of the code? ex.R130.52 If possible, can i pre populate each with the R and have the person enter the rest of the code?

Answer 1

This is not possible. When the user starts typing in a cell, the original content is erased and replaced.

A workaround is to have column B filled with R's, then the user types in the rest of the code in column C, and in column D you have the formula =concat(B1,C1) (example for first row, which you have to copy down of course), and the full code will be displayed. You could make this visually quite nice, by removing grid lines, and make a border around cells of columns B & C, so it seems the R and the rest of code really belong together.

You also can forgo the column B, and change the formula into =concat("R",C1).

All depends how much flexibility you have, how sophisticated the users are, etc.

Answer 2

As otherwise noted, there is no way for Excel itself, meaning the program itself, not add-ons like VBA, to do this.

However, VBA can be used, if you are permitted macros and are willing to learn how to do it. VBA has a mechanism for performing actions based upon the press of a key, or more to the point here: a particular key, the Enter key. So you see the overhead: it has to perform after EVERY keypress. And in your case, it has to decide whether or not to do the action, not just simply do it after every keypress. It will need to check whether the currently selected cell is in column B and so it should perform the action of inserting an "R" before what is currently in the cell, or that it is not column B and so it should not. So a fair amount more overhead.

Additionally, since there are more ways than just this to enter something in a cell, and leave it, say, in a way that Excel will accept the entry as if Enter had been pressed, you would need to suss out all the ways possible (a fair amount of web searching to "catch them all!") and act upon those things being done. Someone might type whatever, then press TAB, or an arrow key, run a macro, mouse away... so many things.

VBA also can do an "OnChange" approach as well, I believe (as opposed to the "OnKey" approach above). But there's still the initial overhead and "do I act?" overhead.

Another approach might be to write a macro to check column B's cells for contents, then whether or not the first character is "R" and to insert an "R" if not, then leave a button for the user to press to do so, and set the macro to also run before Save's of the file. Or to run every minute or ten minutes (or whatever). None of that is really "good" considering the "R" must be important to the spreadsheet's work and so the user might change over to viewing its output without pressing the button, or several minutes before proper updating is done, whatever, and thereby receive wrong results.

One might take a different approach altogether. It really just matters what you are willing to do, what structural changes will be permitted, and whether your users will accept and properly use the new way. For example:

1. "Hey everyone, re-wrote the spreadsheet and you don't need to ever enter an 'R' again... it'll be put in automatically." This has a fun sound, it'll be nifty to enter a number and the "R" appears. It seems like you've taken an incredibly onerous chore off their shoulders. Probably get a lot of happiness with the change.

2. "Hey everyone, re-wrote the spreadsheet and now you must not enter the leading 'R' in each column B entry. If you do enter it, it'll be handled, for a year or two, but then you'll have either gotten with the program or you will be part of the problem." Not such a fun sound, not much nifty about it in the mood it would probably set, and nothing nifty since it's entirely about not doing something, rather than the something still needing done, but being done in a fun way. Probably not much happiness, and maybe some pushback.

So, you know... people.

The way would be to not have the "R" entered at all. But any formula or macro referencing a column B cell would do a "R"&B2386 reference, not a B2386 reference. Probably need to do more than that, in practice, due to "people" and folks still entering the "R" some, or a lot. Something along the lines of always using an IF to test for the leading "R" and outputting either the cell reference (if an "R" was inputted) or the concatenation/joining (if it was not).

But it would no longer be necessary for the leading "R" to be entered which is the goal.

Whoever is maintaining the spreadsheet would have to remember this, always, and eventually, eliminate the extras (simplify things!) and bring fire and brimstone down upon users still entering the "R" sometimes.

So, one set of ways to accomplish the closest you can have of Excel and its related programs doing it for you, and a way to achieve the effect without any programming.

Answer 3

Format the cell, right click the cells- Custom- enter the Type:

RGeneral