Jump to content

In text parsing Removing a specific word or character like a replace function


This topic is 2899 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

  • 1 month later...

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 ; "- " ; "" )
Link to comment
Share on other sites

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??

Link to comment
Share on other sites

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 ] )
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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....

Link to comment
Share on other sites

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 ; ¶ ; " " )
)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2899 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.