Custom Format E.123456 without the "."

[Realtreegirl75]

In my company we have a number that projects are assigned to and it has been typed E.123456 for years. Now, however, we are moving to a different reporting system that means that period can't be in that number. Since everyone is so used to typing that period, I'm thinking it might be easier to format the cell so that the number shows without the period. I've done a fair amount of digging online and can't seem to find what I need.

Is there a way to format this cell so that the number shows E123456 even if someone was to type E.123456?

 

[Joe4]

The problem is that Excel formats only apply to numeric entries, not text ones.

However, you can use VBA to remove any periods as they are being entered.
If you would like to pursue that VBA option, can you tell us which column this project number is being entered into?

 

[Realtreegirl75]

Oh I hadn't thought of using a VBA for that! The E number goes into cell AC3:AJ3 (it is a merged cell in a form that is filled out by multiple people daily)

 

[Joe4]

Firstly, I would HIGHLY recommend getting rid of the merged cell. Merged cells are probably the worst feature of Excel, and cause a whole host of issues, especially for things like VBA.
If just using it across one single line (like you appear to be), a much better option is to use the "Center Across Selection" formatting option instead, which gives you the same visual effect without all the issues. See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Now, back to the matter at hand. Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if update is not to cell AC3
    If Intersect(Target, Range("AC3")) Is Nothing Then Exit Sub
    
'   Get rid of periods in entry
    Application.EnableEvents = False
    Range("AC3").Value = Application.WorksheetFunction.Substitute(Range("AC3").Value, ".", "")
    Application.EnableEvents = True

End Sub

Then, whenever an entry is manually made in cell AC3, it will automatically remove any periods.

 

[Realtreegirl75]

That does exactly what I need it to do! Thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!