7

I'm trying to think of a way to add a leading zero to a string field value. For example I have 12345 but need a formula that would convert it to 012345. I'm new to Crystal as I know that this is probably a simple formula but cant seem to get it to work. 

12345 => 012345  (add leading zero to make it 6 chars)

Thanks in advance.

kuma DK
  • 1,812
  • 1
  • 18
  • 36
Dagz200
  • 221
  • 7
  • 15
  • 25
  • 1
    Are the values always the same length, or do you need pad a number up to a certain number of places with zeroes? For example 12345 becomes `012345` and 2 becomes `000002`. – LittleBobbyTables - Au Revoir Aug 14 '12 at 13:29
  • I used Ryan's answer, but does anyone know if there is an even easier way to do this, without creating a new formula? There is in fact a setting under formatting for number fields called "Leading Zero", but ticking it does nothing. Seems most people finding this answer could use that info too. – MGOwen Jan 29 '13 at 00:44

7 Answers7

7

Try this

totext(your_number, "000000");

1st arg.: Well, it's the input.

2nd arg.: Number of digits you need in the output.

For ex,

num = 1234;

totext(num, "000000");

Output:

001234

And, if you want to add a fixed number of zeroes, then you can just use (to add 3 leading zeroes):

"000" + totext(your_number, 0); // to add 3 leading zeroes

Note: The final output is a string not a number.

Community
  • 1
  • 1
Bhaskar
  • 1,028
  • 12
  • 16
5

To pad a numeric string value with zeroes to a certain length:

local numbervar yournum := tonumber({table.your_string}); //convert to number
totext(yournumnum, '000000') //convert back to padded string of length 6

OR for a universal string

local stringvar yourstring:= {table.your_string};
local numbervar LENGTH := 10; //The desired padded string length

if length(yourstring) >= LENGTH then yourstring else
  replicatestring('0',LENGTH-length(yourstring)) + yourstring
Ryan
  • 7,212
  • 1
  • 17
  • 30
1

how to use the formula in your crystal report, to put below mention formula in ( open (field explorer)-->then right click(Formula field)-->select new--->create formula name(xyz)-->then put ** ToText({DataTable1.Sr_No},"0000") **

copy/past only bold text between ** and then save and close

after that formula you add to your crystal report and run, your required answer is there,

Sr_No. 0001 0002 0003

Khawaja Ikram Hussain
  • 11
  • 1
0

just put on your field '0'+yourstringchar

Guerra
  • 2,792
  • 1
  • 22
  • 32
0

Above the logic works and its a generic formula to left padding

if you want right padding use this

  local stringvar yourstring:= {table.your_string};
  local numbervar LENGTH := 10; //The desired padded string length

  if length(yourstring) >= LENGTH 
        then yourstring 
  else
        yourstring + replicatestring('0',LENGTH-length(yourstring))

--kanthi

user2720864
  • 8,015
  • 5
  • 48
  • 60
user3179181
  • 1
0
Right("00000000"&ToText({Table.correla}),8)
GrandMasterFlush
  • 6,269
  • 19
  • 81
  • 104
user5666314
  • 1
0

I use this:

replace((space((20)-len({Table.Field}))+({Table.Field}))," ","0")

Basically it makes leading spaces, but then replaces those spaces with zeros. By subtracting the length of the field from the added spaces, the field will always be the number of characters specified in space((XX)...

It looks overly-complex, but it actually simplifies reports that require numerous fixed-length fields. As I develop the report, I can copy-paste this code to a new formula, change the number of spaces to match the required field length, then change my field name.

user5849199
  • 1