Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Anonymous
Anonymous asked in 電腦與網際網路軟體 · 1 decade ago

Excel問題

請教excel高手,設有一串17位數字,要如何保留後10位數字,不足10位者前面補0呢?

例:12345670912345678--->0912345678

123456712345678--->0012345678

5 Answers

Rating
  • Anonymous
    1 decade ago
    Favorite Answer

    假設數字在A1, 試試

    B1

    =IF(LEN(A1)<10,TEXT(A1,"0000000000"),RIGHT(A1,10))

    2009-11-20 15:34:58 補充:

    誠如在商言商大大所言, 超過15碼的數字, 須先將其數字前加一單引號 ' , 使其轉為文字格式, 否則第16碼開始的數字均會變為0.

  • 1 decade ago

    選取要設定的儲存格

    功能表上/格式/儲存格/數值/自訂/(類型)輸入17個0/確定

    就可以囉

    希望對你有幫助

    Source(s): 自己
  • 1 decade ago

    設一串17位數字在 A1

    試試:

    =TEXT(RIGHT(A1,10),"0000000000")

  • 1 decade ago

    首先, 先說明一下, Excel 最多只能有 15 位數的精確度,而且會將第 15 位數以後的任何數字捨去成零, 所以, 您要放數字的那格得先設為文字

    數字放A1(記得A欄全改為文字格式), 在A2輸入下列公式就OK囉

    =IF(LEN(A2)>10,LEFT(A2,10),CONCATENATE(REPT("0",10-LEN(A2)),A2))

    2009-11-20 15:37:02 補充:

    LEN(A2)>10, 應該>9 就好了

    2009-11-20 15:39:43 補充:

    LEFT, 要改RIGHT才對, 呵

    2009-11-23 08:58:16 補充:

    =IF(LEN(A2)>10,LEFT(A2,10),CONCATENATE(REPT("0",10-LEN(A2)),A2))

    =IF(LEN(A2)>9,RIGHT(A2,10),CONCATENATE(REPT("0",10-LEN(A2)),A2))

  • How do you think about the answers? You can sign in to vote the answer.
  • 先生
    Lv 5
    1 decade ago

    =RIGHT(A1,10)

    假設17位數字位於A1

Still have questions? Get your answers by asking now.