Take out numbers from texts
Example
In the Excel file book1.xlsx, there is a column of data that is a mixture of characters and numbers. The numbers may appear in multiple places and appear irregularly. The data is shown in the figure below:
Separate all the numbers in each row and place the separated results in column B. The expected effect is as follows:
Write SPL script:
A |
|
1 |
=clipboard().import@i() |
2 |
=A1.(~.words@d().concat()) |
3 |
=clipboard(A2.concat("\n")) |
A1 Read in clipboard data, option @i means read as sequence
A2 Loop each text of A1, use words@d to split all the digits of the text to form a sequence, and then use concat to concatenate each digit together
A3 After concatenating the texts of A2 with \n, put them into the clipboard
Open book1.xlsx, select the A1:A5 area, copy with Ctrl+C, enter esProc to run the SPL script, return to Excel after the run, select B1, and Ctrl+V to paste the results in the clipboard.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
Chinese version