:How to quickly extract cell numbers with text-Table skills免费ppt模版下载-道格办公
投稿
  1. JustNewsHome
  2. Table skills
  3. How to quickly extract cell numbers with text

How to quickly extract cell numbers with text

Author: daogeziyuan • • Table skills • Views 605
  • Picture and text skills


At work, we sometimes encounter tables that contain numbers with text.

So how to quickly extract numbers with text?

Below I will show you how to extract numbers on the left side of the text, extract numbers on the right side of the text, extract numbers in the text, and extract numbers mixed in the text.


教程分界线.png

￭Take this table as an example. When the numbers in the cells are on the left and the text content is on the right, you can use formulas to quickly extract the numbers.

Place the cursor at B2 and enter =LEFT(A2,2*LEN(A2)-LENB(A2)) in the formula bar above.

Place the mouse on the lower right corner of B2 to form a cross-shaped drop-down to fill in the formula, and you can extract it in batches.


1.gif


￭When the number in the cell is on the right and the text content is on the left, place the cursor at D2.

Enter =RIGHT(C2,2*LEN(C2)-LENB(C2)) in the formula bar above.

Place the mouse on the lower right corner of D2 to form a cross-shaped drop-down to fill in the formula to extract in batches.


2.gif


￭How to extract the number in the cell when it is in the middle of the text?

Place the cursor on F2 and enter =MIDB(E2,SEARCHB(?,E2),2*LEN(E2)-LENB(E2)) in the formula bar above.

Place the mouse on the lower right corner of F2 to form a cross-shaped drop-down to fill in the formula, and you can extract in batches.


3.gif


￭If mixed characters such as Chinese, letters, numbers, etc. appear in the cell, how can you quickly extract them?

Place the cursor at H2 and enter in the formula bar above:

=SUM(MID(0&G2,LARGE(ISNUMBER(--MID(G2,ROW($1:$1024),1))*ROW($1:$1024),ROW($1:$308))+1,1)* 10

^ROW($1:$308)/10)

Then use the shortcut key Ctrl+shift+Enter.

Place the mouse on the lower right corner of H2 to form a cross-shaped drop-down to fill in the formula, and you can extract it in batches.


4.gif


Have you learned these tips for quickly extracting numbers from text?



Articles are uploaded by users and are for non-commercial browsing only. Posted by: Lomu, please indicate the source: https://www.daogebangong.com/en/articles/detail/ru-he-kuai-su-ti-qu-dan-yuan-ge-dai-you-wen-ben-de-shu-zi.html

Like (810)
Reward 支付宝扫一扫 支付宝扫一扫
Lomu Nanaaa Columnist
48 196
single-end

Related Suggestion