add a zero if only one digit

[mduffin]

Can MS look at an excel cell and determine if the cell has a one digit number or a two digit number?
If it does have only one digit number, then add a zero?

So, if it's 1-9, then add a zero to the front of the number to make it 01-09?

And I'll have a column of numbers that will differ in amount each day.

So part two I guess is how to make it stop running when there's nothing in the cell?

Thanks in advance!

[CyberCitizen]

I didn't think excel supported 0 (zeros) at the start of a number. Eg if it was a CSV and put into excel it would show as 1 unless you change the format of the cell.

[Grovkillen]

Cyber,

It's not entirely correct, but the only way to over ride this is to add a "'" before the numbers. I.e. "'01" this tells Excel to treat the number as a text. Please observe that Excel still is able to calculate the cell numbers as text:

Cell A1
"'02"

Cell A2
"'03"

Cell A3
"=A1*A2"
Would compute as "6"

[mduffin]

Correct, either way an keep excel from removing the zero..
I've changed the formatting of the columns to text, and now need to add a zero to the single digit numbers.

[Marcus Tettmar]

While Macro Scheduler could do this, it is a bit overkilll when Excel can do it for you using =Text(A1,"00")

[mduffin]

Sorry, maybe I wasn't clear.
The excel sheet already exists (it's a report that gets run every day with very limited options) and it's filled with numbers..
So I switch the format of the columns to text and add a zero to any 1-9's because I need two digit numbers

I don't feel like going through a couple hundred numbers every day and adding a zero if the number is 1-9. So I'm hoping MS can do it for me.

[Grovkillen]

Okay, here's an example I just made.

Before running this make a XLS file named "TEST.XLS" and add numbers to the A coulumn (on sheet "Sheet1"!).
(I added 1-20 for cell A1-A20 and then 1-20 for cell A21-A40).
Save the file to your script dir.

Run this code (please save the script to the same location as "TEST.XLS"):

Code: Select all

XLSheetToArray>%SCRIPT_DIR%\TEST.XLS,Sheet1,SheetArray
XLOpen>%SCRIPT_DIR%\TEST.XLS,0,xlBook
Let>k=0
Repeat>k
  Let>k=k+1
    Let>CellToEvaluate=SheetArray_%k%_1
    If>CellToEvaluate<10
        Let>UpdateCellValue='0%CellToEvaluate%
    Else>
        Let>UpdateCellValue='%CellToEvaluate%
    Endif>
    XLSetCell>xlBook,Sheet1,%k%,1,%UpdateCellValue%,res
Until>k=SheetArray_count
XLSave>xlBook,%SCRIPT_DIR%\TEST.XLS
XLQuit>xlBook

You get the idea... just elaborate to fit your scenario.

[Marcus Tettmar]

You could do it like that but that seems like overkill. One simple Excel formula will do it too. Here's a video of me recording a macro to do it:

https://mjtnet.viewscreencasts.com/5e6f ... 62ed78c318

You could now just run this macro. The macro could always be in another book if you can't modify your source workbook. Macro Scheduler could run the macro if you need it to, or you could convert it to VBScript and run inside Macro Scheduler.

[Grovkillen]

Marcus, most roads lead to Rome. Anyway, what's over kill and not I leave to the rest to judge.

[mduffin]

Thank you both! Marcus, I'm sorry I didn't understand that that little formula would do what I wanted the first time you posted it!

Thanks again!

[mduffin]

What's more.. if you do =TEXT(J9,"1900")
it turns a two digit year into a four digit year!!

Thanks a lot!

[CyberCitizen]

It's not entirely correct, but the only way to over ride this is to add a "'" before the numbers. I.e. "'01" this tells Excel to treat the number as a text.

Thanks for that I didn't know that, always learn something new on MJT Forums.