Home
Help
Search
Calendar
Login
Register
Yet Another Cycling Forum
»
General Category
»
The Knowledge
»
Ctrl-Alt-Del
»
adding leading zero formula for excel
« previous
next »
Print
Pages: [
1
]
Author
Topic: adding leading zero formula for excel (Read 563 times)
chrisbainbridge
adding leading zero formula for excel
«
on:
July 18, 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
Logged
Pingu
Put away those fiery biscuits!
Mrs Pingu's domestique
Re: adding leading zero formula for excel
«
Reply #1 on:
July 18, 2018, 11:23:46 am »
Something like:
Code:
[Select]
=IF(MOD(LEN(SUBSTITUTE(A2," ","")),2)>0,"0"&A2,A2)
Logged
A crap website
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:
July 18, 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
Logged
Greenbank
Re: adding leading zero formula for excel
«
Reply #3 on:
July 18, 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.)
Logged
"Yes please" said Squirrel "biscuits are our favourite things."
Ham
Re: adding leading zero formula for excel
«
Reply #4 on:
July 18, 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)
Logged
Ben T
What you saying, then?
Re: adding leading zero formula for excel
«
Reply #5 on:
July 18, 2018, 11:39:14 am »
=LEFT("0",MOD(LEN(A1),2)) &A1
Logged
I do find that slightly bizarre, I must admit.
Ham
Re: adding leading zero formula for excel
«
Reply #6 on:
July 18, 2018, 12:09:00 pm »
neat
Logged
chrisbainbridge
Re: adding leading zero formula for excel
«
Reply #7 on:
July 18, 2018, 01:25:21 pm »
Thank you
I will try tomorrow.
Chris
Logged
chrisbainbridge
Re: adding leading zero formula for excel
«
Reply #8 on:
July 19, 2018, 10:11:22 am »
Ben T
Worked like a dream
Thanks
Logged
Print
Pages: [
1
]
« previous
next »
Yet Another Cycling Forum
»
General Category
»
The Knowledge
»
Ctrl-Alt-Del
»
adding leading zero formula for excel