Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

8 characters format

0

Good Afternoon,

 I would like to insert an ID card number with 8 characters. Example iI wish to insert the ID card number. 12345M I would like to tbe inserted as 0012345M. Simply when I type 12345M the cell reads 0012345M. Can you please help me to solve it

Yours truly,

Ray Gatt

Answer
Discuss

Answers

1

I found a way by formating the individual cell(s)  (left click on cell, then right click on cell, down to format cells,"Number tab", then "Custom" and then enter into the box 0000# or however many 0's that you require with the hash symbol.

When you enter a single number it will return the number with say 0001 when you press return.

Discuss
0

I think the first answer is probably what you want, but, another option is to put a single quotation mark in front of the number before you type it. However, this will make Excel see it as Text instead of a number.

Example:

'0012345M
Discuss
0

Your own solution puts you on the correct path but it can't do what you have described. Note that the format 000# will display zero as 000. If you wish to display zero as 0000 the cell format should also be 0000.

Still with the format 000#, if you enter 1M you will get a string of "1M" ( displayed leftbound). You might use 000#\M to display a number (rightbound) 0001M but the "M" is hard-coded and must not be entered by the user.

There is no cell format solution I know or can find which would give you a string of random numbers plus characters filled with leading zeroes to a total width of 8 characters. The formula below will convert a string entered unformatted In A1 to the desired format in another cell.

=TEXT(LEFT(A1, LEN(A1)-1),"0000") & RIGHT(A1,1)

This formula works if the number is followed by a single character. It could be tweaked - at some considerable effort - to accommodate any number of trailing letters, reducing the number of leading zeroes to always create a length of 8 characters.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login