How to Extract Numbers from Text Data

 

Problem description

In an Excel file, there is a column (as shown below) containing values consisting of characters and numbers. The positions of numbers in these values are different and has no specific rule to follow.

undefined

We are trying to extract all numbers from each row and put them into a column B, as shown below:

undefined

Directions

1.   Select area A1:A5 in the Excel file and press Ctrl+C to copy it.

2.   Back to esProc to paste the data to cell A1.

3.  Write the following code to do the task:

A

B

1

……(Data   pasted from Excel)

2

=A1.import@i()

3

=A2.(~.words@d())

4

=A3.(~.concat())

A2   The import() function works with @i option to import A1’s text data as a sequence.

A3   words() function works with @d option to extract all numbers from each member of the sequence in order.

A4   Concatenate all numbers in each row as a string.

4.   After the code is executed, copy A4’s content to paste it to B1 in Excel.

 

Q & A Collection

https://stackoverflow.com/questions/61718800/excel-extract-all-numbers-from-a-string