I have data within a field which consists of measurements that I want to extract. for example

 

ProductSpecification

 

Fork TS Air 27.5", Remote Lockout

Fork TS Air 30 27.5", Remote Lockout, 100mm

Fork TS Air 30 27.5", Remote Lockout, 120mm

Fork TS Air 30 27.5", Remote Lockout, 140mm

 

Doing a simple PatternCount is fine for say "100mm" but how can I do this for an entire range (between 0 and 500mm). For example PatternCount ( ProductSpecification; >500mm )

 

Thanks

You are not providing enough details to understand the task. What exactly do you want to extract, and what is known for sure to exist in the field that would enable us to locate the wanted part/s?

 

 

P.S. I hope those are 4 individual records in your example, not 4 lines in the same record...

P.S. I hope those are 4 individual records in your example, not 4 lines in the same record...

 

Hope springs eternal …

Apologies, 

 

I am wishing to extract the sizing information from these records (they are individual records)

 

For example

 

Fork TS Air 27.5", Remote Lockout = "0"

Fork TS Air 30 27.5", Remote Lockout, 100mm = "100mm"

Fork TS Air 30 27.5", Remote Lockout, 120mm = "120mm"

Fork TS Air 30 27.5", Remote Lockout, 140mm = "140mm"

 

I have created a new field called ProductSpecificationSizing which will include this information. What I am looking for is the calculation so I can attract the information from these records and 1000's more. 

I am afraid that's still rather vague. Perhaps you could use =

GetAsNumber ( RightWords ( ProductSpecification ; 1 ) )

which would return the numeric values without the units (if the units are known, then this is the better approach). Otherwise try =

Let (
lastWord = RightWords ( ProductSpecification ; 1 )
;
Case ( lastWord ; lastWord )
)

However, both will fail if your field contains, for example: "Fork TS Air 30 27.5", Remote Lockout 3DVision". This could be prevented if you know for sure that the measurement, if present, is always the last word and contains some digits followed by "mm".

I can confirm that the measurements present will always be followed by "mm" but can't guarantee that they will appear at the end of the record

That is problematic, because a calculation that looks for "mm" at the end of a word, will be fooled by "comm" or "stumm". There aren't many words that end with "mm", but to make this foolproof you must loop over each word in turn and see if it ends with "mm" AND contains only digits before that.