Extract Numeric Values

Extract numbers from a variable that contains a mix of text and numbers.

  1. Enter the name of the variable from which you want to extract numbers

  2. Specify how you'd like the results to be presented:

    • Indicate if you'd like the formats of the numbers from the original variable to be maintained in the output

    • Choose the delimiter you'd like to use in the output to separate the numbers extracted from the original variable

  3. Enter the name of the variable into which you'd like to place the extracted numbers

  4. Instruct the wizard how to handle any errors encountered. Read more about error handling.

How exactly are numbers extracted?

The wizard recognizes numbers as separate from text by looking for specific characters within a string of data. In order for a number to be properly identified, it must be enclosed in quotes (either single or double) or separated from the surrounding text by one of the following characters:

  • <Space>

  • Comma ( , )

  • Semicolon ( ; )

  • Pipe ( | )

The wizard uses the same characters to recognize numbers as separate from each other – with the important exception of commas (because many numbers utilize commas as part of their formatting).

Try it out: To ensure that numbers will be extracted as you expect, try some test data. Simply click the NUMERIC EXTRACTOR TESTER link from within the EXTRACT NUMERIC VALUES command.

A word about currency symbols

For purposes of the EXTRACT NUMERIC VALUES command, the wizard will recognize a number immediately preceded by a currency symbol (e.g., $, €, £, ¥, etc.) as a numeric value.

If you elect to preserve original number formats (and there is no space between a number and the currency symbol preceding it), the currency symbol will be preserved in the output.

Example

Let's say you run an e-commerce website. At the end of each day, you read a list of all the items ordered into a variable called daily items ordered. The downloaded data includes item description, stock number, unit price, and quantity ordered. For order fulfillment purposes, you need only the stock number, unit price, and quantity ordered.

daily items ordered =

Item Description, Stock Number, Unit Price, Quantity Ordered

Tennis Racquet, 6527895, €65.00, 10

Tennis Shoes, 6387429, €89.50, 15

Tennis Balls, 6572369, €0.85, 90

Tennis Shorts, 6354789, €14.00, 20

Result:

daily items numeric =

6527895;€65.00;10;6387429;€89.50;15;

6572369;€0.85;90;6354789;€14.00;20