formula for truncating. 2

[teddysnow12]

Hi..

I need a formula for truncating first two zeros of each number in series of numbers.

my numbers lokk like this:

group ids : 0046854 , 0083669 ,0097787


Thanks!

 

[fisheromacse]

MID({table.Field},2)

 

[teddysnow12]

thanks for your reply.

but the output for this formula looks like this :
046854 , 0083669 , 0097787

I want the out like :

46854 , 83669 , 97787




Thanks!

 

[MCuthill]

teddysnow,

Is there always a " , " between all the numbers?

If so, this will work:

Replace(Mid({@Field},3),", 00",", ")

Test:
0046854 , 0083669 , 0097787

Becomes:
46854 , 83669 , 97787

Logic:
Take the field, starting at the 3rd character until the end of the field. Then find all occurances of ", 00" and replace it with ", ".

If there is not always the " , " between all the entries, this approach WILL NOT work.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[teddysnow12]

thanks for the reply,

the output for this formula truncated the zeros for first number only.

46854,0083669,0097787


I do have the commas between each number in the series.



Thanks!

 

[MCuthill]

TeddySnow,

Please clarify as to where this data is obtained:
"0046854 , 0083669 , 0097787"

I placed this value in a formula (exactly as above) and the solution worked. If they are all individual records, fisher's solution (with 3 instead of 2) will work.

Also, please copy and paste the exact contents of your formula field for review. From your reply, it only seems to have replaced the spaces, which makes no sense based on the formula I provided.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[teddysnow12]

This is basically the output of an other formula :

numbervar max := count({?customer - VAL_5});
numbervar k := 1;
stringvar output := "";
for k := 1 to max do (output := output +{?customer - VAL_5}[k]+",");
numbervar m := len(output);
left(output,m-1)


this formula returned me the result as these series of numbers with commas separated and NO double codes.

Now, I want these numbers without zeros as I mentioned in previous posts .



Thanks!

 

[MCuthill]

Thanks Teddy,

There are no spaces padding the comma in your results... which is why the formula did not work.

Change my prior formula to as follows:

Replace(Mid({@Field},3),",00",",")

OR

I think you can also change your code above to as follows (though I am unable to test for exact syntax):

numbervar max := count({?customer - VAL_5});
numbervar k := 1;
stringvar output := "";
for k := 1 to  max  do (output := output + Mid({?customer - VAL_5},3)[k]+",");
numbervar m := len(output);
left(output,m-1)

Hope this helps.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

numbervar max := count({?customer - VAL_5});
numbervar k := 1;
stringvar output := "";
for k := 1 to max do (
output := output + totext(val({?customer - VAL_5}[k]),0,"")+","
);
numbervar m := len(output);
left(output,m-1)

-LB

 

[teddysnow12]

Thank you so much.

It worked!!!

 

[MCuthill]

@TeddySnow: Which solution did you use? (as I am curious if applying the Mid() logic in your original formula worked) Can you post the solution used?

@LBass: Love the solution LBass! Excellent for if there are ever more (or less) than 2x leading zero's. Never thought to covert the string to a value (removing all zero's), then coverting back to a string.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[teddysnow12]

Actually both the codes worked.


Mcuthill:
Replace(Mid({@Field},3),",00",",")

Lbass:
numbervar max := count({?customer - VAL_5});numbervar k := 1;stringvar output := "";for k := 1 to max do (output := output + totext(val({?customer - VAL_5}[k]),0,"")+",");numbervar m := len(output);left(output,m-1)


Thanks!

 

[MCuthill]

Thanks Teddy.

LB, Have a Star!!

Question for you LBass.
Would the solution I provided, using Mid() in place of your ToText(Val()) approach work (granted, assuming all records always had 2 leading zeros), or would it need to be modified (cast field as text inside the mid())?

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Yes, if there are always two leading zeros and if you move the subscript:

numbervar max := count({?customer - VAL_5});
numbervar k := 1;
stringvar output := "";
for k := 1 to max do (
output := output + Mid({?customer - VAL_5}[k],3)+","
);
numbervar m := len(output);
left(output,m-1)

-LB

 

[MCuthill]

Thanks LB!

I haven't worked with Arrays in Crystal and wondered where the subscript would be placed. Tied to the field, not the function... which makes perfect sense.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."