Software

Ask something
Question Locked

excel spreadsheet

0 helpful
2 replies

by dmauk

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am trying to get the cell to fill in digits to the right if missing a number of digits. i.e. If I have a number in one cell 10-200-86-570 and the next cell has 10-200-86-570-1, I want to set up another cell that will fill in the first cell so it ends up as 10-200-86-570-0000, and the next cell as 10-200-86-570-1000, meaning that I need to fill in the right side with zeros so that all have the same number of digits. is this possible?

2 replies

by tonythetiger

Put '00-000-00-000-0000 in a hidden cell, let's name that cell "Template"

The user types in the first column, Call it column A

The resulting value will be in the next column, call it Column B

In the top cell of column B:

IF(A1="","",LEFT(A1,LEN(A1))&RIGHT(Template,LEN(Template)-LEN(A1)))

(all on one line)

then copy the value down as many rows as you need. This column should be write protected.

What it does

If A1 is blank (nobody has typed anything in yet), B1 is blank

If A1 has x characters, B1 has that character, plus the right-most 18-x characters of "Template"

if the length of A1 is more than 18 characters, B1 will show a #VALUE error.

(If you find this useful, please mark it with a thumb so that others can more easily find it)

This discussion is closed to new replies.