6 Replies Latest reply on Jun 19, 2012 9:15 AM by jo.cheaney

# How do I apply filter to pick out a number within a long reference.

Hi

I have a field REFERENCE eg. 0164R02T1300211.  The 4th number (4) relates to the day of the week ie. Thursday.

How do I write something to return that digit only.

Thanks

• ###### 1. Re: How do I apply filter to pick out a number within a long reference.

Hey Jo, this should work:

INT(RIGHT(LEFT(STR([REFERENCE]),4 ),1))

--Shawn

• ###### 2. Re: How do I apply filter to pick out a number within a long reference.

Hi

Thanks for that.  I am however struggling to find a way of teaching myself these things.  My job is analysing large amounts of data and I am used to Access which is logical.

• ###### 3. Re: How do I apply filter to pick out a number within a long reference.

Jo, I hear you. Every software has it's own logic (and limitations), even Tableau. Trying to figure out how to answer questions like yours is the way I learned a lot of this. That and reading the answers other folks post. Most of this I hardly ever need or use, but when I do I sure don't want to be trying to figure it out on a deadline!

Hang in there, it gets easier.

--Shawn

• ###### 4. Re: How do I apply filter to pick out a number within a long reference.

Hi

sorry I got an error message

I did cut and paste your answer but it says The Calculation contains errors and when I click on it, the formula must be a boolean formula!

Any ideas

• ###### 5. Re: How do I apply filter to pick out a number within a long reference.

Jo, it sort of depends on how you're trying to use it. The formula is correct (see attached) it does return the forth digit. But lots of other stuff can be effecting the results. Can you post a packaged workbook so we can see how you're trying to use it?

--Shawn

1 of 1 people found this helpful
• ###### 6. Re: How do I apply filter to pick out a number within a long reference.

Hi

Thanks I have got it to work.  It was because I had put the formula in the wrong place ie. on the filter tab and when I did it as Create Calculation it worked.