Visokio website     Downloads     Video tutorials     KnowledgeBase  
Text Wildcards in Formulae - Visokio Forums
Text Wildcards in Formulae
  • Dibbhead March 26, 2010 4:39PM
    Hi there,

    I have used the standard Excel * as a text wildcard in a formula but it does not work. Is there a different wildcard in Omniscope for any number of characters?

    I have searched the help topics and can't find an answer to this.

    Thanks
  • 6 Comments
  •     tjbate March 28, 2010 5:42PM
    Could you provide a little more context/detail on what situation/problem you are trying to solve using a wildcard, and the equivalent Excel expression?
  • Dibbhead March 29, 2010 9:28AM
    Hi there,

    Thanks for getting back to me. What I mean is the following example:

    I have 3 fields called 'Behavioural: Conversion', 'Behavioural: Buzz' & 'Competitive:Spend'.

    I am trying to create a formula which populates a new field where the metric starts with 'Behavioural'. Therefore I am trying to do an IF statement update where the metric is 'Behavioural*' and where the star is any number of characters following the word behavioural.

    The star (*) is the any number of characters wildcard in excel.

    Thanks
  •     steve March 29, 2010 9:32AM
    Can you give an example of the full formula you would expect to use in Excel, please?
  • Dibbhead March 29, 2010 11:43AM
    In Excel, if cell a1 starts with Beh then populate otherwise leave blank:

    =if(A1="Beh*",A1,"")

    Omniscope version (identical and offers no errors):

    IF([Metrics]="Beh*", [Metrics],"")
  •     steve March 29, 2010 12:53PM
    As far as I'm aware, you can't do this in Excel. IF does not support wildcards. Some functions do (like COUNTIF), but in any case, Omniscope does not support these.

    You can achieve the desired effect using SEARCH as the first clause of your IF statement:
    http://www.visokio.com/functions-guide#SEARCH

    E.g. IF( SEARCH("Beh*", [Metrics], 1)=1, [Metrics], "" )
  • Guy_Cuthbert        Guy_Cuthbert March 30, 2010 3:05PM
    The easiest way to achieve the desired result here is to use the LEFT function to compare then leftmost n characters with a desired string (in this case, "Beh") i.e.

    IF(LEFT([Metrics],3)="Beh",[Metrics],"")

    Should do the job nicely.
    Atheon Analytics Ltd
    w: www.atheonanalytics.com
    e: guy.cuthbert@atheon.co.uk
    t: +44 8444 145501
    m: +44 7973 550528
    s: guycuthbert

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership