Shulou(Shulou.com)11/24 Report--

Original title: "8 must be efficient extraction number method, you know how many?"

Hello everyone, I am addicted to learning VBA Xiaoshuang ~

Recently, I have a little fan--

One day, when I was playing on my computer, WeChat suddenly rang twice. On closer look, it turned out to be cute Diyuan Song:

Should be far song for help, today I will give you a brief introduction--the use of functions, quickly extract the number of cells!

01, numbers and Chinese characters There is a separator between numbers and Chinese characters [Ctrl+E]& the method of sorting is very familiar to everyone, so I won't repeat it here ~

Here we can also use the find function to find delimiters.

Enter the following formula in cell C2, and then drop down to fill in the last one.

=LEFT(B2,FIND("-",B2)-1)

Small resolution: use the find function to find the location of the separator, and then use the left function to extract numbers from the left.

There is no separator between numbers and characters

As above, you can also use the shortcut key [Ctrl E] to separate.

We can also use the lenb+len function to obtain location information and extract numbers.

As shown in the figure below, the len function takes English as 1 byte, while the lenb function takes Chinese characters as 2 bytes.

Therefore, we enter the following formula in the F2 cell and drop down to fill it:

=LEFT(E2,LEN(E2)*2-LENB(E2))

Small analysis:

LEN (E2)*2-LENB (E2) is the number of digits on the left side, truncated with the left function.

Numbers are on the right side of Chinese characters.

When the number is on the right side of the Chinese character, we can use len+lenb+mid.

The input formula is as follows:

=MID(H2,LENB(H2)-LEN(H2)+1,99)

Small analysis:

LENB (H2)-LENB (H2) is mainly to obtain the number of left text, plus 1 is the number of digits of the first number.

Truncate from the first position to 99 digits (as long as the number of digits extracted is greater than or equal to the maximum number of digits).

Numbers are in the middle of Chinese characters.

This is done using the lookup function with the array formula.

The formula is as follows:

=-LOOKUP(1,-MIDB(K2,SEARCHB("? ",K2),ROW($1:$15)))

Small analysis:

Here is the use of SEARCHB function wildcard search function, to find the number in the string.

Because the formula is an array formula, you need to press Ctrl+Shift+Enter to perform array operations.

When it comes to mixing numbers, letters and Chinese characters together, we need to use complex array formulas.

Array formula needs to be pressed to perform array operations, and braces appear on the left and right sides of the formula;

If you're in the latest preview version of Office 365, just press Enter.

Extract the string of consecutive numbers on the left and right

=-LOOKUP(1,-RIGHT(O2,ROW($1:$15)))

The numbers are on the far left.

=-LOOKUP(1,-LEFT(R2,ROW($1:$15)))

Small analysis:

First, use the right function to intercept substrings with lengths from 1 to 15 from right to left, and then use the negative operation to convert the text to an error value and convert the numerical value to a negative number;

Then use the lookup function to get the last value of the array, and finally turn negative into positive.

The principle of numbers extracted from the left is the same as that extracted from the right.

Numbers in arbitrary positions

=-LOOKUP(0,-MID(K2,MIN(FIND(ROW($1:$10)-1,K2&1/17)),ROW($1:$15)))

Small analysis:

First use ROW function to construct a numeric array of 0-9:

ROW($1:$10)-1

"1/17" equals 0.0588235294117647, which is a value containing all numbers from 0 to 9, concatenated at the end of the text to prevent the FIND function from returning the wrong value when looking for numbers because the text lacks the corresponding number.

FIND function to find 10 numbers in the text position, use MIN function to return the text appears in the minimum position of the number, that is, you can get the position of the first number.

Use the MID function to intercept 15 substrings of length 1~15 from the first digit position to the right, add negative signs, convert numbers to negative numbers, and convert text to error values.

Finally, use LOOKUP function to return the largest number, and then use negative sign to convert negative numbers to positive numbers.

With the help of the max function (only integers can be extracted).

The formula is as follows:

=MAX(IFERROR(--RIGHT(LEFT(K20,ROW($1:$13)),COLUMN($A:$D)),0))

Small analysis:

First use len function to determine the longest number in string.

row (1:13)--13 This number is only required if it is greater than the longest number in the string, and the purpose is to intercept the next subcharacter.

LEFT (K20,ROW ($1:$13) --Truncate 1 - 13 substrings from the first character to the right.

column (a:d)--a:d depends on the maximum number of digits in the string.

RIGHT (LEFT (K20,ROW ($1:$13)),COLUMN ($A:$D)--truncate 1 - 13 subcharacters from left to right, and then truncate 1 - 4 subcharacters from right to left from this array. In this case, it is a 13*4 array.

The formula is preceded by--to convert the text to the wrong value.

Convert the error value to 0 and select the largest value from the array to get the largest number.

After a while, I asked Yuan Ge,"

How do you usually extract numbers from cells?

This article comes from Weixin Official Accounts: Autumn Leaf Excel (ID: excel100), Author: Xiao Shuang Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.