In Google Sheets, you can create a custom format where you force a cell to display a specific number of digits.įor example, you can format the cells in such a way that it always shows 6 digits. Create a Custom Format with Fix Number of Digits This will make sure the text formatting is intact and works as expected. Important: When you copy and paste numbers that have leading zeros into those cells that have been formatted as text, make sure you paste these as values. Now when you enter a number which has leading zeroes, Google Sheets will keep them. It will make some additional options show up on the right. Hover the cursor over the Number option.Select the cells for which you want to change the format.Once you have done this, you can type a number of leading 0’s (or copy and paste such values into these cells) and it will not lose those numbers at the beginning.īelow are the steps to change the format of the cells to text: Note: Even though adding an apostrophe changes the number to a text, you can still use it in formulas and calculations as if it is a number Change the Cells format to TextĪnother quick way to keep the leading zeros is by changing the format of the cells so that anything that you put in it is considered text.
This is because now Google Sheets considers it as text (by default text aligns to the left and numbers to the right in Google Sheets). When you add an apostrophe at the beginning of the number, you would notice that it aligns to the left. So if you want the value in the cell to be 000123, you should enter ‘000123.Īdding an apostrophe converts the number you enter into text, and the leading zeros don’t go away. This works well when you have to manually enter the data in the cells.Īll you need to do is first type an apostrophe (‘) symbol and then type the number. Let’s deep dive into each of these methods.
#Google sheets cell plain text format how to#
Let’s get started! How to Keep Leading Zeros in Google Sheets
There are multiple methods for keeping leading zeros in Google Sheets, and you can choose the method that works for you (these same methods would also work in Microsoft Excel). That’s exactly what I am going to show you in this tutorial. This is especially useful when you’re working with phone numbers data or data such as ids or social security numbers. For example, if you enter 000123, it will automatically remove the first three 0’s and only give you 123 as the result.īut what if you want to keep the leading zeros in Google sheets? When you enter a number in a cell in Google sheets that has leading zeros, it will automatically remove those leading zeros. If you work with numbers in Google Sheets, there is one thing that can often frustrate many users.
0 kommentar(er)
