Insert dashes only when needed 1

[TimothyP]

I have a part # that varies in length.
The part number is 3, 5, or 8 characters in length.
When it’s 3 chars, I want to display 123.
When it’s 5 chars, I want to display 123-45.
When it’s 8 chars, I want to display 123-45-678.

Can someone help me create a formula to insert the dashes when needed?

Thanks in advance for the knowledge.

 

[dgillz]

If trim(Length({PartNumber}))=3 then {PartNumber} else if trim(Length({PartNumber}))=5 then picture({PartNumber},"XXX-XX&quot else if trim(Length({PartNumber}))=8 then picture({PartNumber},"XXX-XX-XXX&quot

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[bdreed35]

Try this:

WhilePrintingRecords;
stringVar PartNo = "";

if Length({table.partno}) = 5 then
    PartNo := Left({table.partno},3) + "-" + 
              Right({table.partno},2) 
else if Length({table.partno}) = 8 then
    PartNo := Left({table.partno},3) + "-" + 
              Mid({table.partno},4,2) + "-" + 
              Right({table.partno},3)
else
    PartNo := {table.partno};

PartNo;

~Brian

 

[bdreed35]

dgillz:

I didn't even think about using the Picture function. Nice solution. The only thing is that I think you wanted to use the trim on the {PartNumber} field before you used the Length function.

If Length(trim({PartNumber}))=3 then {PartNumber} else if Length(trim({PartNumber}))=5 then picture({PartNumber},"XXX-XX") else if Length(trim({PartNumber}))=8 then picture({PartNumber},"XXX-XX-XXX")

~Brian

 

[dgillz]

bdreed35,

You are 100% correct, The trim() statement needs to go inside the length() statement.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[TimothyP]

Thanks for the solution dgillz/bdreed35.
It's working like a champ!!!

 

[mbarron]

Here's one that doesn't use if-then's

stringvar num:=trim({your.field});
stringvar out:=picture(num,"xxx-xx-xxx"
left(out,truncate(len(num)*1.25))

Mike

 

[Naith]

You only really need to cater for the minimum or maximum length with the Picture function.

If Length(Trim({field})) > 3
Then Picture({Field},'xxx-xx-xxx')
Else {Field}

Naith

 

[mbarron]

Naith,
If there is a five digit number, your formula results with an extra dash at the end 12345 results as 123-45-

Mike

 

[bdreed35]

Mike:
Nice approach. I would not have though about doing it that way, but it certainly simplifies it.

Naith:
I tried your solution and it leaves trailing hyphen when the length is 5.

~Brian

 

[Naith]

D'oh.

You are, of course, absolutely correct. That'll teach me to barge in on other people's threads like I think I own the joint.

 

[bdreed35]

Naith:

Not a problem. I like when other users post different solutions, especially when it simplifies things. I tend to learn alternate solutions that I may have either never used or have overlooked.

~Brian