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.
We are trying to extract all numbers from each row and put them into a column B, as shown below:
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.
https://stackoverflow.com/questions/61718800/excel-extract-all-numbers-from-a-string
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 http://c.scudata.com
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
