之前描述不太清楚重新發一個,想請教一下如何提取Excel儲存格某特定字符後出現的數字
例如
2023-01-19 Jay cash buy in NT100,000 at mall 我只提取100,000
2023-01-19 Jay cash buy in NT100K at mall 我需要提取為 100,000
2023-01-19 Jay cash buy in NT1M at mall 我需要提取為1,000,000
現在卡在怎麼將金額的K定義為×1000 M定義為×1000000
因為不同同事做的標註不一樣 我需要提取數字金額加總
限365版本
B1:
=LEFT(TEXTAFTER(A1,"NT"),FIND(" ",TEXTAFTER(A1,"NT"))-1)
不好能再請教一下
2023-01-19 Jay cash buy in NT100K at mall 我需要提取為 100,000
2023-01-19 Jay cash buy in NT1M at mall 我需要提取為1,000,000
怎麼將金額的K定義為×1000 M定義為×1000000,現在卡在這裡
B1:
=TEXT(SUBSTITUTE(SUBSTITUTE(LEFT(TEXTAFTER(A1,"NT"),FIND(" ",TEXTAFTER(A1,"NT"))-1),"K","000"),"M","000000"),"#,###")
如果要數字類型。
B1:
=VALUE(SUBSTITUTE(SUBSTITUTE(LEFT(TEXTAFTER(A3,"NT"),FIND(" ",TEXTAFTER(A3,"NT"))-1),"K","000"),"M","000000"))
謝謝大佬
A1=2023-01-19 Jay cash buy in NT100,000 at mall
B1=MID(A1,FIND("NT",A1)+2,FIND(" ",A1,FIND("NT",A1))-FIND("NT",A1)-2)