Forum Discussion
Add Letter Into Number
I am wanting to see if there is a way to have a letter inserted into a cell that already has a number if another cell meets a certain criteria. See below for example.
Cells in Column D are prefilled with a number. Once the adjacent cell in column L is filled, I would want it to put a letter in Column D after the 25 as you see. Anyone know if this is possible? If so what would the formula be? I have racked my brain trying to create it but I'm coming up empty.
3 Replies
Adding a letter to an existing cell is not allowed in a functional programming language such as the Excel formula language. Conditional formatting changes the way in which a number is displayed without changing the underlying number. Other than that, you will need an imperative programming language such as VBA. A Worksheet Change Event would be able to read column D and replace it with something else because state changes are an integral part of such languages.
As HansVogelaar​ has already said, you can't have a fixed value and a formula in the cell at the same time. He gave you one solution.
Another solution would be to have the "raw numbers" (i.e., the as yet unused incident numbers) off to the side. Then, when an incident occurs with Luke Skywalker or Minnie Mouse, you create the "lettered incident number" in your column D.
See the attached as an example. Column D contains a formula, nested in an IF such that the resulting full incident # only gets completed when all the other data are entered. It's just a proof of concept. I assumed a different letter each time, for example; if your reality calls for a certain letter based on conditions you know, you could write another formula to generate that letter.
A cell cannot contain a fixed value and a formula at the same time. You might do the following:
- Enter the values in column D as 6-digit numbers: 252572, 252573, etc.
- Apply the custom number format 00"-"0000 to the values, so that they will be displayed as 25-2572 etc.
- Select D2:D100 (or as far down as needed).
- D2 should be the active cell in the selection.
- On the Home tab of the ribbon, click Conditional Formatting > New Rule...
- Select 'Use a formula to determine which cells to format'.
- Enter the formula =L2<>""
- Click Format...
- Activate the Number tab.
- Select Custom as category.
- Enter 00"H-"0000 in the Type box.
- Click OK, then click OK again.
This will always insert the letter H. If you want to insert different letters, you'd have to provide a rule to determine when to use which letter.
