RaqForum 19 No.
1 Reply  •  335 View  • 
     ...

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:

undefined

Separate all the numbers in each row and place the separated results in column B. The expected effect is as follows:

undefined

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.


 
 •  335 View  •