Is there any way to "pad" numbers | Official Apple Support Communities
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
That is, I have a series of 6 digit numbers like this, that are padded with zeros from the left
001234
003456
034567
but when I paste them into Numbers, they look like this
1234
3456
34567
This is a real problem because there are a LOT of these and I can't convert to text and then edit/correct them one by one. In Excel, there's a custom format that allows you to indicate that there should be 6 (or however many) digits, and which will automatically pad (from the left) to meet that need. I can't seem to find this (or any other workaround) in Numbers.
7 replies
Sign in or join Beachball Help to reply.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Welcome to the club.
It would be fine to search in existing threads before opening a new one.
This question was asked and responded MANY times.
I uses some tricks
replace the standard zeroes by slashed ones.
As numbers neading to keep their leading zeroes are in fact strings, it is not a problem Ø622Ø for instance is my zip code
Ø493654321 is a kind of phone number used in France.For phone numbers, you may also use 04 93 65 43 21. With the embedde spaces, it's no longer a number but a string which keeps its leading zero.
When I know that those which will read my documents are reluctant to slashedZeroes, I use a nonBreakable space at the end of the number (we get it with alt + space). With it, the number will be treated as a string.
Yvan KOENIG (from FRANCE mardi 18 mars 2008 17:30:04)
- 0
degraham ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I did search the forums. Unfortunately searching on "pad" and "numbers" comes up with virtually every reference in the forum. "Pad" alone comes up with a lot of stuff about the number pad... 8(
If you can suggest a set of better search terms, I would be most grateful.
Also, your (otherwise excellent) solution will not work for me - I am pulling up data from a database and have no control over the numbers' spacing or format of the zeros. I need a more direct solution, unfortunately.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
degraham wrote:
I did search the forums. Unfortunately searching on "pad" and "numbers" comes up with virtually every reference in the forum. "Pad" alone comes up with a lot of stuff about the number pad... 8(If you can suggest a set of better search terms, I would be most grateful.
Also, your (otherwise excellent) solution will not work for me - I am pulling up data from a database and have no control over the numbers' spacing or format of the zeros. I need a more direct solution, unfortunately.
I apologize but on this forum you are meeting users trying to help other users.
We can't change the behaviour of an application._Go to "Provide Numbers Feedback" in the "Numbers" menu_, describe what you wish.
Then, cross your fingers, and wait _at least_ for iWork'09You may also use this script.
(*
Enregistrer le script en tant qu'Application ou Progiciel : padWithZeroSlashed.app
déplacer l'application créée dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.Copier les nombres à formater dans le presse-papiers
Avec la version étendue, vous pouvez travailler sur plusieurs colonnes.
Placez le curseur où ça vous chante dans Numbersmenu Scripts > padWithZeroSlashed
Le script collera les nombres formatés selon le masque ØØØØØØØ.
+++++++
Save the script as an Application or an Application Bundle: padWithZeroSlashed.app
Move the newly created application into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.Copy the numbers to format in the clipboard
With the enhanced version you may work on several colums.
Put the cursor where you wish in Numbersmenu Scripts > padWithZeroSlashed
The script will paste the numbers formatted with the mask ØØØØØØØ.
Yvan KOENIG le 18 mars 2008
*)
--(SCRIPT padWithZeroSlashed.app]property lignesAvant : missing value
property lignesApres : missing value
property listeCells : missing value
property mask : missing value
property nbChars : 5on run
set theApp to "Numbers"
my nettoie()
try
set txtDatas to the clipboard as Unicode text
on error (*
The clipboard was empty *)
return
end try
set my lignesAvant to paragraphs of txtDatas
if my parleAnglais() is false then
set msg to "Nombre de caractères souhaité ?"
else
set msg to "Pad to how many digits ?"
end if
tell application (path to frontmost application as string) to set len to choose from list {"4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "15"} default items {nbChars as text} with prompt msg
if len is false then return (* click on "Cancel" *)
set nbChars to len as integer
repeat nbChars times
set mask to mask & "Ø"
end repeatif txtDatas contains tab then
repeat with n in my lignesAvant
set my listeCells to my decoupe(n, tab)
set r to ""
repeat with i from 1 to count of my listeCells
if i < (count of my lignesAvant) then
set r to r & my applyMask(item i of my listeCells) & tab
else
set r to r & my applyMask(item i of my listeCells)
end if
end repeat
copy r to end of my lignesApres
end repeat
else
repeat with n in my lignesAvant
copy my applyMask(n) to end of my lignesApres
end repeat
end if
set the clipboard to my recolle(my lignesApres, return)
tell application theApp to activate
tell application "System Events" to tell application process (my whichProcess(theApp)) to keystroke "v" using {command down}
my nettoie()
end run--=============
on applyMask(nn)
local s
copy nn to s
if s is not "" then
set s to text -nbChars thru -1 of (mask & nn)
end if
return s
end applyMask--=============
on decoupe(t, d)
local L
set AppleScript's text item delimiters to d
set L to text items of t
set AppleScript's text item delimiters to ""
return L
end decoupe--=============
on recolle(L, d)
local t
set AppleScript's text item delimiters to d
set t to L as text
set AppleScript's text item delimiters to ""
return t
end recolle--=============
on nettoie()
set my lignesAvant to {}
set my lignesApres to {}
set my listeCells to {}
set mask to ""
end nettoie(* =============
An application named "NumbersAnglais.app" may be already running,
the script has activated "Numbers.app"
but the two apps named their process "Numbers".
We must check the "displayed name" to identity the correct one
*)
on whichProcess(the_App)
local listProcess, running, i
tell application "System Events"
set listProcess to (displayed name of processes)
repeat with i from 1 to count of listProcess
set running to 0
if item i of listProcess is (the_App & ".app") then
set running to i
exit repeat
end if
end repeat
end tell -- to System Events
return running
end whichProcess--==================
on parleAnglais()
local z
try
tell application "Pages" to set z to localized string "Cancel"
on error
set z to "Cancel"
end try
return (z = "Cancel")
end parleAnglais--==================
--[/SCRIPT]
Yvan KOENIG (from FRANCE mardi 18 mars 2008 19:38:22)
- 0
robinmonies ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Format the cells as Text before pasting. Numbers will maintain the leading 0's, but beware that your numbers are now text and some functions won't work with them (others might).
A1 contains 00123
A2 =sum(A1,1) gives 1
A3 =A1+1 gives 124You can also pad numbers with something like
A3 contains 124
=CONCATENATE(CHOOSE(LEN(A3),"0000","000","00","0",""),A3)
which gives 00124
Again, this is text so beware.
If needed, you can put the formula in an IF function to avoid an error if more or less digits are entered than what you expected. The base formula I gave above will return an error if A3 is blank or contains 123456.
I'd fix that with something like
=IF(len(A3)<1,"not enough digits",if(len(A3)>5,"too many digits",CONCATENATE(CHOOSE(LEN(A3),"0000","000","00","0",""),A3))gluck.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Hello
The problem with every tip padding with standard zeroes is that these zeroes are not permanent.
A simple copy paste is able to drop them.This is why, as when padding is required the values are not used as true numbers, I think that padding with slashed zeroes or adding a nonBreakable space is a better soluce.
Of course, every user may choose this or that but it seems that it is important to know the consequences of the choice.
Yvan KOENIG (from FRANCE mercredi 19 mars 2008 13:55:49)
- 0
Jerrold Green1 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Yvan,
What would be the problem with simply formatting the input cell to Text? Seems too easy.
Jerry
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Hello
It's true that with Numbers the problem is not so severe that it is with AppleWorks.
With this coelacanthus, copy/paste is sufficient to strip the leading zeroes.
In Numbers they are kept.But,
try to apply the script posted yesterdays to filter columns from the bottom checkboxes.
The leading zeroes disappear.And the script does only that:
set val to the clipboard as text
set the clipboard to valIf you want to be able to use AppleScript to enhance Numbers (and I hope that there will be an AppleScript dictionary in Numbers v2), it seems useful to learn "good" habits at the very beginning.
Changing them later is often painful. It's a 64 years old guy which write that. I often type RANGÉE( ) when Numbers uses LIGNE( ).Yvan KOENIG (from FRANCE mercredi 19 mars 2008 18:56:53)

