EXTRACT NUMBERS IN EXCEL CHARACTERS

Use left or right in combination with the find function.

Take the preceding one: left(a1,find("*",a1)-1) after right(a1,len(a1)-find("*",a1))

If you want the result to be numerical, you can add --

become=--left(a1,find(.)"*",a1)-1)

Select the whole column, point data - column - separator symbol - other, enter * in the box at the end, and you're done.

How to extract the numbers in ** separately? Just copy-paste.

Data disaggregation. Select the column to be processed by column, click the menu "Data Column", and select the separator symbol "*".

1. Open Excel** on the computer and enter the formula =right(c5,len(c5)*2-lenb(c5)).

2. Press the Enter key to determine the formula, and you can extract the numbers.

3. Enter the formula =left(c6,lenb(c6)-len(c6)).

4. You can extract the Chinese characters.

How to extract numbers before and after * characters in excel.

The numeric value in front of the character.

01, B2: The data in cell B18 contains only one * character, and the number of numbers in front of the * character is different, some are 3 digits, some are 4 digits, and some are 5 digits. In this case, we need to use the left function and the find function.

02. Select the cells in the C2:C18 area; Next, enter the formula in the field: =left(b2,find("*b2)-1), after entering the formula, we press the keyboard's :

Ctrl+Enter, these two keys. c2:c18 is to display the extracted numbers at the same time; These numbers are all b2:

The number in front of the b12 cell data * character.

<>03 Formula Explanation: =left(b2,find("*b2)-1) We can understand that the left function has a truncation function, which can intercept the number of numbers specified on the left in the specified cell, and find has a search function, and the * character is found in the cell on the number of digits of the data. For example, in cell b2, we find out that the * character is in the 5th position, and then, subtract 1, which is the number of bits we need to intercept, and use the left function to intercept it.

The numeric value after the character.

01, B2: B18 cell data contains only one * character, and the number of numbers in front of the * character is not the same, some are 3 digits, some are 4 digits, and some are 5 digits. In this case, we need to use the right function, the len function, and the find function.

02. Select the cells in the C2:C18 area; Next, enter the formula in the field: =left(b2,find("*b2)-1), after the scrambler leaks into the formula, we press the keyboard's :at the same time

Ctrl+Enter, these two keys. c2:c18 is to display the extracted numbers at the same time; These numbers are all b2:

b18 cell data * the number in front of the character.

<>03 Formula explanation: =right(b2,(len(b2)-find("*b2))) We can understand it this way, the right function has a truncation function, you can intercept the number of numbers specified on the right in the specified cell, len(b2) calculates how many bits of data there is in this cell, find has a search function, find out the * character in the cell is on the first few digits of the data, the total number of data bits minus the number of data bits where * is located to get the remaining number of digits. For example, if there are 8 values in cell B2 and the * character is in the 5th position, there are 3 digits after the * value, which is the number of digits we need to intercept.

1.Extract the first cell numberIn Excel, extract the first cell number manually. 2.

1. Open Excel** and enter the internal companion of the extracted value in column A cell.

2. Enter the formula of the function in cell B1: =left(A1,len(A1)-5), which means take the value in A1 as the basis and remove the 5 digits from the bottom of A1.

3. Click the Enter key to generate the calculation result of the function formula, and the characters in the last five digits of the A1 cell have been removed in the figure below.

4. Drag the copy formula downwards to extract part of the data in column A in batches.