Excel leading zeros query...

Scripting Forums AppleScript | Mac OS X
#1

Hi there,

I have a column of data in Excel that looks something like the list below, each value in a separate cell.

018126
018127
204288
204296
204319

I’ve been using

set x to formula of range ("A1:A50") of active sheet

to get the values out of the required range.
It works fine for nearly all the numbers however if there’s a leading zero that get missed and I need to retain that.

Is there a way round this? I’ve tried a number of different variations on a theme but as yet haven’t solved it.

Thanks,

Nick

#2

Hi,

try string value instead of formula

#3

Hi Stefan,

Thanks for the help with this query.

I’d tried ‘string value’ which works fine for a single cell e.g.

set x to string value of range ("A1:A1") of active sheet

If however I increase the range to (“A1:A50”) I get ‘missing value’ in the result window of the script editor?
Is there a reason for this?

Thanks again Stefan.

Regards,

Nick

#4

What are you doing with the data?

Numbers don’t have leading zeros, only strings do.

If you are pasting them into other cells, you can paste the numbers and then set the number format of those cells to “000000”.

#5

Thanks for the help.

I need to retain the leading zero where they occur for the next stage.

I’m not sure why I’m getting the ‘missing value’ error now?

#6

Formatted Excel text (as with leading zeros) is held in the Range.Text property, not in the Range.Value or Range.Formula properties.

#7

Thanks for the help Mick. Are you talking VBA wise?

#8

Hi Tec -

I am. Here’s how i pulled zips from Column(R), Row(i):
set StudentZip to string value of cell (“Edit!R” & i)

If you’re still having trouble, here’s a routine I use:


on form(N, x, type)
	set N to N as text
	set y to length of N
	if type is true then
		set pad to character id (8199)
	else
		set pad to "0"
	end if
	if x is greater than y then
		repeat with i from 1 to x - y
			set N to pad & N
		end repeat
	end if
	return N
end form

Character id (8199) is “figure space”, a space the same width as the digits. For you, you’d want: form(N,5,false)
If you have the free Satimage OSAX (http://www.satimage.fr/software/en/downloads/downloads_companion_osaxen.html) it has a built-in format function.

…Mick

#9

Thanks again for the help Mick.

It looks like you’re looping through each cell one at a time however I’d being trying to get the cell values from a range. I think that’s why my code was throwing an error.

Regards,

Nick

#10

Hi Mick,

fyi, I think this was the easy way (and probably slightly quicker) of adding leading zeros:


set n to 123
set padded_number to text -5 thru -1 of ("00000" & n)

where n may be an empty string.

gl,

#11

Kel - that was just neat. Thanks.

Tec -
Applescript only has lists. To get an array into a list requires lists-of-lists. This entry maybe germane:

https://discussions.apple.com/thread/2726499?start=0&tstart=0

…Mick