Standardizing Data Format Formula

[serpent333]

Hello everyone. I have a spreadsheet with a column that lists room numbers for a hotel....some that are three digit, and some that are four. In order to derive an accurate floor number for these data entries, I need the room numbers to be reproduced in a new column, with a formula that determines how many characters the "ROOM" field data, is and then adds a leading zero to those room numbers that are only 3 digits in length.

Example:

If room is 1439, new column should simply reproduce the data unchanged: "1439"
If room is 745 (3 digit), formula should reproduce room number as "0745"

Thanks in advance!

[VBA Noob]

You could custom format the columns as

0000

or use a formula like

=TEXT(A1,"0000")

VBA Noob

[serpent333]

Hey Noob, thank you but the program im working with is on MS SharePoint which does not allow me to change the format of input ranges. However, once the data has been submitted in a Free Form Text fomat, I can create "calculated" columns that allow me to format the data....which is why I think I need an IF statement that works something like:

IF number of characters in cell range = 3, result = "0 + data", otherwise the data should be unchanged

[VBA Noob]

An if formula won't work if this didn't

=TEXT(A1,"0000")

VBA Noob