If they are always in the same place (exact number of characters from the start), then you can use mid() in three different calculations. If something varies then you may need to use len() to modify which character you start at, or even find() to get the position of "Frequency" for example.
Unfortunately the starting position is variable and I don't actually need the string Frequency itself. I just need the value assigned to it e.g. 'M'.
I guess I need to create three different calculations, one for each. I just have no clue where to start. Potentially the only useful consistency is the fact that value is always after an '=' and before an '&'. However, there are several of those, so how can it determine which one to look for?
1 of 1 people found this helpful
If you know where frequency is, you'll always know where M is.
For example, this will give you the frequency value
Find frequency (letter 24), add 10 (the letters of frequency plus the =) and use mid to give me the single letter at that position.
Thanks, that worked very well for the Frequency and Reporting Period where the number of characters are fixed. But for Reporting Service desnt always have same number of characters. How can the length be determined if its a substring?
Is the first & always the one after the reporting services? If so, you can find the position of this to get the length of the string.
ok thanks, since the value for reporting services is also variable length, I managed to work out the length by using the formulae below. Luckily the value was always after "=" and before a "&":
mid([CS_Query],find([CS_Query],"ReportingService")+17,(find([CS_Query], "&")-1)-(find([CS_Query], "=")))