Mid Index

[dhruvipanchal88]

I have list of ID and need to categorize them. 

If ID starts with 6 then  it is EMS

If ID starts with 8 then Fire

If ID start with AB100, AB 101 then MUW.

I tried using Mid Index formula.  -  mid(Unit,Index(Unit,'7'),4) AS FireUnits,. Then It counts if any ID has 7 included in. I need to count ID starts with 7.

Please Suggest.

[Vegar · Accepted solution]

This might help you with your  issue.

pick(wildmatch ([ID],'6*','8*','AB100*','AB101*'), 'EMS','Fire','MUV','MUV')

[dhruvipanchal88]

@Vegar 

It works. Thanks for the help.

for MUV, I have several number AB100, NV100, NV101, BV100,BV101 and many more. First two are alphabets and last three are digits range between 100 to 108. Can you provide any solution for that?

[Vegar]

You need to find one or multiple patterns that covers your MUV IDs. Maybe like this:

pick(wildmatch ([ID],'6*','8*','??10*'), 'EMS','Fire','MUV')