How do I apply a prefix using only one cell

[KentBurel]

I have a snippet of a bigger spreadsheet that looks like this

The problem is quite simple. I want to enter a 7 digit seal number where the first 4 digits are the same. So instead of having to enter all 7 digits, I want to only enter the last 3 and have the 4 digit prefix automatically prepended. And I need to do this in the same cell. So when the user clicks on a blank cell C2 and enters 123, I want that cell to prefix it with 0543 (or whatever the current prefix is) and store that value in cell C2. I don't want the E and F columns. This is part of a larger VBA project so I don't have to use formulas, I can use VBA code as well.

Thank you for your help.

 

[sandy666]

I don't have to use formulas, I can use VBA code as well.

so what do you want to use???

 

[KentBurel]

I think I want to use a cell select event and apply the prefix there. But how do I retrieve the three digits the user entered there? I really don’t want to present an input box for each cell. Perhaps a deselect box would work better. I’m so new that I don’t know which of these options is better or even something else.

 

[HappierThan]

G'day mate,

Format cell C2 to C100 (or last cell) and choose "Custom" and in "Type:" put in 0543000.

If you ONLY type 3 numbers, (for instance 123,) you will get 0543123.

Cheers H

 

[Peter_SSs]

you will get 0543123

Just to clarify, that will appear in the cell but it won't actually prepend the 0543 so if the 7-digit result is to be used elsewhere there may be problems.
It will also produce some unusual results if the user actually manually enters the 7-digit number in the first place.

@KentBurel
This uses the Worksheet_Change event and will prepend even if multiple cells are entered at once (eg by Ctrl+Enter, Copy/Paste etc
If the entry is more or less than 3 characters the entry stays as entered.
I have assumed that any required prefixes are listed in column A as in your sample.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim Prefix As String
 
  Set Changed = Intersect(Target, Columns("C:D"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Prefix = vbNullString
      If c.Rows > 1 And Len(c.Text) = 3 Then
        On Error Resume Next
        Prefix = Columns("A").Find(What:=Cells(1, c.Column).Value, LookAt:=xlPart).Offset(, 1).Text
        On Error GoTo 0
        With c
          .NumberFormat = "@"
          .Value = Prefix & c.Text
        End With
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub