4 Replies Latest reply: May 15, 2014 9:53 AM by donh _

I have been trying just about everything and can't seem to get the last piece - - -

id1 column needs to be id2 with 5 characters - 3 to the left of the decimal (if there is a decimal) and 2 to the right of the decimal (unless it begins with the letter E then it is just 1 to the right).  Cells that are blank/empty need to be blank/empty:

[font="courier"]id1     id2

E923     E9230

E849.5      E8495

642.21     64221

997.5     99750

998     99800

9     00900

11.8     01180

V05.3     V0530

V02.51     V0251[/font][/quote]my formula so far does not put the zero's in the correct place for xxx.x data --- it resuls xxx.0x

[font="courier"]if((Len())=0,chr(0),

if((Left(,1))=&quot;E&quot;,

(LSplit(,2,&quot;.&quot;,1)right(&quot;0&quot;trim(LSplit(,2,&quot;.&quot;,2)),1)),

LSplit(,2,&quot;.&quot;,1)left(&quot;00&quot;trim(LSplit(,2,&quot;.&quot;,2)),2)))[/font][/quote]Can anyone help?

Thanks

Don

/size[quote]code:[/size]if(left(id1,1)=&quot;E&quot;,

left(Strip(id1,&quot;.&quot;)+&quot;00000&quot;,5),

if(instr(&quot;.&quot;,id1)&gt;0,

right(&quot;00000&quot;lsplit(id1,2,&quot;.&quot;,1)left(rsplit(id1,2,&quot;.&quot;,1)+&quot;00&quot;,2),5),

if(instr(&quot;.&quot;,id1)=0,right(&quot;00&quot;id1&quot;00&quot;,5),right(&quot;00000&quot;lsplit(id1,2,&quot;.&quot;,1)left(rsplit(id1,2,&quot;.&quot;,1)+&quot;00&quot;,2),5)

))) /QUOTEMy brain hurts!

This works for all of your sample data.

If someone thinks there's a better way, be my guest. Please. Simplify this monster.

Edit: I missed the "blank must be blank" requirement.

The expression then becomes:

/size[quote]code:[/size]if(id1=&quot;&quot;,&quot;&quot;,if(left(id1,1)=&quot;E&quot;,

left(Strip(id1,&quot;.&quot;)+&quot;00000&quot;,5),

if(instr(&quot;.&quot;,id1)&gt;0,

right(&quot;00000&quot;lsplit(id1,2,&quot;.&quot;,1)left(rsplit(id1,2,&quot;.&quot;,1)+&quot;00&quot;,2),5),

if(instr(&quot;.&quot;,id1)=0,right(&quot;00&quot;id1&quot;00&quot;,5),right(&quot;00000&quot;lsplit(id1,2,&quot;.&quot;,1)left(rsplit(id1,2,&quot;.&quot;,1)+&quot;00&quot;,2),5)

))))  /QUOTE

PERFECT - - - BIG THANKS for your time

Don

I think the following might also give you the result you are looking for:

[font="courier"]replace(if(left(ID1,1).in.(&quot;E&quot;,&quot;V&quot;),left(strip(ID1,&quot;.&quot;)+&quot;00000&quot;,5),str(val(ID1)*100,5,0,&quot;0&quot;)),&quot;00000&quot;,&quot;&quot;)[/font][/quote]

Bill:

That did work too

THANKS

Don