Sometimes i would like to remove a comma from a field

sometimes i might want to remove the word "of" in a field

 

What is the simplest way to do this?

 

"Sometimes" is not a word you can translate to computer code. On those occassions that you want to remove all occurrences of a known string from text, use the Substitute() function to substitute the known string with an empty string "" .

Comment,

I know you werent trying to be funny but this made me laugh.

Thanks for breaking up the day!.

I know you werent trying to be funny

 

You can never know.

It made me laugh too.....  But interestingly, he understood and gave me a great answer. The funny thing is the concept he engaged by saying 'sometimes' doesn't translate into computer code.  It would really be interesting to see how he might tackle 

the word 'meanwhile' 

i think that has no translation as well.

But it sure is interesting

What's the criteria to determine when you want to do this?  Or are you asking how does one parse for a comma or specific word in a calculation.

the word 'meanwhile' 

i think that has no translation as well.

 

That one actually might: http://en.wikipedia.org/wiki/Do_while_loop

Hi folks,

I'm trying to use Substitute in a calculation field as below.

I think it would work on its own - do I just need to put the 1st four or last line in brackets to make it work?

Instrument1  &"-"&  Instrument1Grade  &" "&
Instrument2 &"-"&  Instrument2Grade &" "&
Instrument3  &"-"&  Instrument3Grade &" "&
Instrument4 &"-"&  Instrument4Grade
Substitute ( text ; "- " ; "" )

Ah, after a bit of experimentation I've worked out where the brackets go!

Substitute ( Instrument1  &"-"&  Instrument1Grade  &" "&
Instrument2 &"-"&  Instrument2Grade &" "&
Instrument3  &"-"&  Instrument3Grade &" "&
Instrument4 &"-"&  Instrument4Grade;"- " ; "" )

I still end up with a few space marks where kids don't learn an instrument; I'll have a go at putting "- " OR "  " ; ""

Is there a better way to do this??

Try 

Let (
  theList =
    List ( 
      Case ( Count ( Instrument1 ; Instrument1Grade ) = 2 ; Instrument1 & ": " & Instrument1Grade ) ;
      Case ( Count ( Instrument2 ; Instrument2Grade ) = 2 ; Instrument2 & ": " & Instrument2Grade ) ;
      Case ( Count ( Instrument3 ; Instrument3Grade ) = 2 ; Instrument3 & ": " & Instrument3Grade ) ;
      Case ( Count ( Instrument4 ; Instrument4Grade ) = 2 ; Instrument4 & ": " & Instrument4Grade ) ;
    ) ;
 Substitute ( theList ; ¶ ; " " )
)

Is there a better way to do this??

 

The usual: fields numbered 1, 2, 3, 4… indicate the need for a normalized structure (i.e. a “child” [or join] table). See how repetitive that calculation is …?

 

Ah, after a bit of experimentation I've worked out where the brackets go!

 

If I'm not mistaken, these characters [ ] are brackets, and these ( ) are paren(these)s. Be sure to distinguish between them, because both can appear in the same function / calculation, e.g. in your very own Substitute():

Substitute ( myText ; [ string1 ; replacement1 ] ; [ string2 ; replacement2 ] )

Many thanks eos,

I was wondering if using list would work in this instance, but couldn't work out how it would work - I'll have a go with yours now.

 

I will be putting instruments into a separate table in the next few months - that's one reason I wanted this calculation table in place - so the layouts where it's needed would already be sorted.

 

Cheers!

Mike

Ah - eos we have a problem.

I /think/ it's because the Count is expecting a value in Instrument1Grade when there's one in Instrument1, but that often isn't the case.

I've checked that for kids without Instrument?Grades at all, there's no list, and when there are instruments without grades, these aren't listed either.

I could fix this by replacing any blank field with something like N/A....

OK; this seems to work - does it create any problems I can't spot?

Let (
  theList =
    List ( 
      Case ( Count ( Instrument1 ; Instrument1Grade ) ≠ 0 ; Instrument1 & ": " & Instrument1Grade ) ;
      Case ( Count ( Instrument2 ; Instrument2Grade ) ≠ 0 ; Instrument2 & ": " & Instrument2Grade ) ;
      Case ( Count ( Instrument3 ; Instrument3Grade ) ≠ 0 ; Instrument3 & ": " & Instrument3Grade ) ;
      Case ( Count ( Instrument4 ; Instrument4Grade ) ≠ 0 ; Instrument4 & ": " & Instrument4Grade ) ;
    ) ;
 Substitute ( theList ; ¶ ; " " )
)

OK; this seems to work - does it create any problems I can't spot?

 

No – except that for the Boolean purpose of Case(), Count ( Instrument1 ; Instrument1Grade ) ≠ 0 is the same as Count ( Instrument1 ; Instrument1Grade ).

 

But seeing as you can have instruments entered without a grade (but not vice versa!), and want to list them all – grade or no grade – try

Let (
  theList =
    List ( 
      Case ( not IsEmpty ( Instrument1 ) ; Instrument1 & ": " & Case ( Instrument1Grade ; Instrument1Grade ; " - " ) ) ;
      Case ( not IsEmpty ( Instrument2 ) ; Instrument2 & ": " & Case ( Instrument2Grade ; Instrument2Grade ; " - " ) ) ;
      Case ( not IsEmpty ( Instrument3 ) ; Instrument3 & ": " & Case ( Instrument3Grade ; Instrument3Grade ; " - " ) ) ;
      Case ( not IsEmpty ( Instrument4 ) ; Instrument4 & ": " & Case ( Instrument4Grade ; Instrument4Grade ; " - " ) ) ;
    ) ;
 Substitute ( theList ; ¶ ; " " )
)

assuming that the InstrumentNGrade fields are of type Number, which – unlike Text – can be queried directly for its Boolean value.

How about....

 

Let ( [
Instr1 = Substitute ( List ( Instrument1 ; Instrument1Grade ) ; "¶" ; ": " ) ;

Instr2 = Substitute ( List ( Instrument2 ; Instrument2Grade ) ; "¶" ; ": " ) ;

Instr3 = Substitute ( List ( Instrument3 ; Instrument3Grade ) ; "¶" ; ": " ) ;

Instr4 = Substitute ( List ( Instrument4 ; Instrument4Grade ) ; "¶" ; ": " ) ;

result = Substitute ( List ( Instr1 ; Instr2 ; Instr3 ; Instr4 ) ; "¶" ; " - " )

];

result

)

How about …

 

Right; that's better.

Thanks both - it works really well now :-)

 

I'm hoping that doing a find on this  calculated field should be quicker than a quick find on the 4 previous instrument fields - is that correct?

 

Cheers,

Mike

Oh yes, it's miles faster - nice bonus :-)