How Excel uses functions to extract numbers

Many people familiar with Excel data processing will deeply feel the powerful software features, especially if the use of functions, it is not only to make things easier, but also let colleagues feel that they are really an Excel master. Today's small series to extract the text and numbers mixed together in the data, the use of function only to extract the number of methods to explain the use of Excel tutorial, we feel the feeling of learning!

1, the Chinese characters in the front, the number in the back of the situation. You can use Mid,min, find three functions to extract the number inside. Figure: In B2 input "=mid (A1,min (Find ({0,1,2,3,4,5,6,7,8,9},a1&" 0123456789)), 20) "

2, put all the numbers in the cell. You can use macros. Open the VBA editor first. Tools--Macro--visual Basic Editor

3, in the editor click Insert--module. Enter the following code in the module:

Function Zzsz (Xstr as String) as StringDim i as integerfor i = 1 to Len (XSTR) If isnumeric (Mid (Xstr, I, 1)) Then Zzsz = Zzs Z & Mid (Xstr, I, 1) nextend Function

4. Return to the worksheet and enter "=zzsz (A2)" in the B2 cell. You can use VBA to extract all the numbers in the A2 cell, as shown in the figure.

How Excel extracts only the numbers in a table

5. Specifies to start from the first few array extracts. That is, in the discontinuous numbers, the array begins to be extracted from the first few occurrences. Also enter the following code in the module:

Function getnums (Rcell as Range, num as Integer) as StringDim Arr1 () As String, ARR2 () as StringDim chr as String, Str as StringDim I as Integer, J as Integeron Error GoTo line1

STR = Rcell.textfor i = 1 to Len (str) CHR = Mid (str, I, 1) If (ASC (CHR) Or ASC (CHR) THENSTR = Replace (str, CHR, " ") End IfNext

ARR1 = Split (Trim (STR)) ReDim Arr2 (UBound (ARR1)) for i = 0 to UBound (Arr1) If Arr1 (i) "" "ThenArr2 (j) = Arr1 (i) j = j + 1End IfNext

Getnums = IIf (num = j, Arr2 (num-1), "") Line1:end Function

6, in return to the cell where the input "=getnums (a3,2)" Can extract the first occurrence of the array, as shown in the figure.

Above is my peacetime and everybody learns the experience and everybody share, hoped for everybody to have the help. If it is useful to you, please vote for my encouragement.