13.10.11

Extract number from a string

To extract a string of number with a particular length (for example a string of 3 numbers) from a cells we will use the expression match and regex language. This is an easy four steps process



  1. Create a new column based on the column you want to extract your string of number (in Edit column > Add column based on this column)
  2. Key the following in the dialogue box:
    value.match(/.*(\d{3}).*?/)[0]
  3. Update the value 3 with the length of the string you want to extract. 
  4. A new column will be created with your string of number. 


You can use the replace function to remove the string from the original field using this expression:
replace(value, value.match(/.*(\d{3}).*?/)[0], '')


Tip from the discussion list.