Custom Formatting in Excel with Letters and Numbers

[fornie]

I am trying to use custom formatting in excel to show the following format for account numbers that I use:
E-9511-I200-100-610
The E is always constant, but the place where the I is typed can be either I or S or A.
I can format for everything by the variable letter. So far, I have come up with "E""-""####"-"###"-"###"-"### but I don't know how to complete it to accept the variable letter. Please Help!

[misrasomendra]

Hi,

What is the condition for I, S and A?

[Bernie Deitrick]

You are not entering a number - you are entering a string, and there are far fewer formatting options for strings. But, you could use the worksheet change event to convert the entry to the proper form. Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. Then save the workbook as a macro-enabled .xlsm As written, the code will check entries in cells B2:B100 - enter the values without the E: 9511I200100610

Please Login or Register  to view this content.

[fornie]

I am not sure that I understand your question. There is no condition. Depending upon the account # that I using (typing in), that place in the account number will either be an I, A or S. I am trying to format the cell so that I only have to type in the numbers and the one variable letter and not be bothered typing in the E and -.
So if I type in 9511I200100610 it will show as E-9511-I200-100-610; if I type in 9511S000262420 it will show as E-9511-S000-262-420, etc.

[Bernie Deitrick]

Custom formatting will not work in your situation. See my post for a solution.

[fornie]

Thank you, thank you, thank you!!!! I did it and it worked.