Author Topic: adding leading zero formula for excel  (Read 1220 times)

adding leading zero formula for excel
« on: 18 July, 2018, 11:07:59 am »
I am trying to work on an excel column.  The data is exported from an old database as a CSV file and strips out the leading zeros automatically when it writes the csv file unfortunately

Hospital unit numbers are always an even number of digits.  They are padded with a leading zero if they are an odd number of digits. Hospital numbers can vary in length from 6 digits to 10 digits

So   36 36 45 is a hospital number
10 01 10 10 is a hospital number
1 56 57 59 is not.

I need to identify that the cell has an odd number of characters in it and if it is odd add a leading zero. 

I could do it by hand but I have 7500 rows!  Can anyone recommend a formula to do it for me?
Thank you

Pingu

  • Put away those fiery biscuits!
  • Mrs Pingu's domestique
    • the Igloo
Re: adding leading zero formula for excel
« Reply #1 on: 18 July, 2018, 11:23:46 am »
Something like:

Code: [Select]
=IF(MOD(LEN(SUBSTITUTE(A2," ","")),2)>0,"0"&A2,A2)

PaulF

  • "World's Scariest Barman"
  • It's only impossible if you stop to think about it
Re: adding leading zero formula for excel
« Reply #2 on: 18 July, 2018, 11:27:13 am »
=ISODD() will tell you if a number is odd or even, =COUNT() will tell you how many characters.

Does your data set include the spaces?

If so I would create a copy of the numbers in a new column, use copy/replace to strip out the spaces. Then =ISSODD(COUNT(ref)) will show TRUE for all values with an odd number of digits. You can then filter on that and proceed from there. Adding zeroes could be automated depending how many you have

Re: adding leading zero formula for excel
« Reply #3 on: 18 July, 2018, 11:32:25 am »
=CONCATENATE(IF(ISEVEN(LOG10(A1));"0";"");FIXED(A1;0;1)) seems to work for me:-

1 -> 01
12 -> 12
123 -> 0123
1234 -> 1234
12345 -> 012345
123456 -> 123456
1234567 -> 01234567
12345678 -> 12345678
123456789 -> 0123456789
1234567890 -> 1234567890

=CONCATENATE(IF(ISODD(LEN(A1));"0";"");FIXED(A1;0;1))

does the same without LOG10 stuff.

(Note: Mine is OpenOffice, might need tweaking for Excel if it prefers , to ; for separators.)
"Yes please" said Squirrel "biscuits are our favourite things."

Re: adding leading zero formula for excel
« Reply #4 on: 18 July, 2018, 11:36:06 am »
Assuming you want a string, =if(isodd(len(cell)),"0"&text(cell,0),text(cell,0)) would be simplest

(edited to replace the space with zero)

Ben T

Re: adding leading zero formula for excel
« Reply #5 on: 18 July, 2018, 11:39:14 am »
=LEFT("0",MOD(LEN(A1),2)) &A1

Re: adding leading zero formula for excel
« Reply #6 on: 18 July, 2018, 12:09:00 pm »
neat

Re: adding leading zero formula for excel
« Reply #7 on: 18 July, 2018, 01:25:21 pm »
Thank you

I will try tomorrow.
Chris

Re: adding leading zero formula for excel
« Reply #8 on: 19 July, 2018, 10:11:22 am »
Ben T

Worked like a dream

Thanks