Excel日期处理、字符串处理用到的公式记录

2016年02月25日 其他资源 6条评论 阅读6718次

Excel日期处理、字符串处理用到的公式记录


Excel公式博大精深,容易学同时也容易忘。这次在做一个工作时间统计用的表格文档时,用到了有关于日期处理、字符串处理等相关的公式,记录一下。

前提说明

  • C6 单元格内容:2016 年
  • E6 单元格内容:1 月份
  • A12单元格内容:1
  • A9 单元格内容:汇总月(1月)

取得C6(数字字符串混在一起)单元格的左侧数字部分

LEFT($C$6,5)

取得E6(数字字符串混在一起)单元格的左侧数字部分

MIDB($E$6,1,2) PS:最后一个参数2表示,从E6单元中取得两个字符 因为有10月这样 月数为两位数的情况

年月日分开的三个单元格重新组合为日期格式

DATE(LEFT($C$6,5),MIDB($E$6,1,2),LEFT(A12,2))

自定义显示该日期为星期几

TEXT(DATE(LEFT($C$6,5),MIDB($E$6,1,2),LEFT(A12,2)),"aaa") PS:aaa是单元格格式中自定义的

从A9单元格字符串中提取数字

=MID(LEFT($A$9,SEARCH("月",$A$9)-1),5,2)

字符串拼接

"string1"&"string2"

取得excel中sheet的名称

REPLACE(CELL("filename",INDIRECT("A1")),1,FIND("]",CELL("filename")),"")


分享本文至:

WRITTEN BY

avatar
本文标签:excel
看了本文是不是觉得很赞,那就赶紧点击下面按钮分享给身边的朋友吧!

6 条评论

  1. avatar lmcgroep

    <a href="http://www.flushnhunt.com/air-jordan-after-game-cielo-blu-scarpel">air jordan after game cielo blu</a> <a href="http://www.evelpa.com/womens-nike-roshe-yeezy-boost-orange-gold-shoesj">womens nike roshe yeezy boost orange gold</a> <a href="http://www.taaliminfos.com/nike-hyperrev-r%C3%B8d-sort-skoi">nike hyperrev r酶d sort</a> <a href="http://www.cacikvideo.com/air-jordan-son-of-mars-cielo-blu-nero-scarpeq">air jordan son of mars cielo blu nero</a> <a href="http://www.grossavind.com/mens-nike-dunk-all-orange-shoesj">mens nike dunk all orange</a> <a href="http://www.boltsfans.com/michael-kors-bronze-clutch-mkh">michael kors bronze clutch</a>
    lmcgroep http://www.lmcgroep.com/

  2. avatar torrentslot

    <a href="http://www.sofakolle.com/wei%C3%9F-rot-nike-shox-r4-herren-laufen-schuhe-schuhek">wei脽 rot nike shox r4 herren laufen schuhe</a> <a href="http://www.tomsavagetrio.com/nike-free-5.0-verkauf-scarpeg">nike free 5.0 verkauf</a> <a href="http://www.hopeforcinema.com/kvinners-asics-gel-noosa-tri-10-gull-gr%C3%B8nn-kurpesb">kvinners asics gel noosa tri 10 gull gr酶nn</a> <a href="http://www.pusulagelisim.com/nike-air-force-1-mid-negro-gym-rojo-zapatosk">nike air force 1 mid negro gym rojo</a> <a href="http://www.ndtkalashop.com/ua-curry-3-orange-braun-runningc">ua curry 3 orange braun</a> <a href="http://www.isgmembers.com/nike-blazer-mid-rojo-high-top-trainers-zapatosj">nike blazer mid rojo high top trainers</a>
    torrentslot http://www.torrentslot.com/

  3. avatar hutchinsmedia

    <a href="http://www.bazicloob.com/alle-rot-converse-high-tops-schuheh">alle rot converse high tops</a> <a href="http://www.malatyabolge.com/supra-skytop-white-pavement-canvas-shoes-shoesk">supra skytop white pavement canvas shoes</a> <a href="http://www.impeachjohn.com/adidas-tubular-radial-sort-burgundy-skoj">adidas tubular radial sort burgundy</a> <a href="http://www.dafuqstory.com/nike-zoom-winflo-3-wei%C3%9F-gr%C3%BCn-schuheh">nike zoom winflo 3 wei脽 gr眉n</a> <a href="http://www.liberezmoi.com/light-blue-asics-gel-lyte-iii-shoesk">light blue asics gel lyte iii</a> <a href="http://www.scrutinit.com/michael-kors-leather-backpack-sale-mkh">michael kors leather backpack sale</a>
    hutchinsmedia http://www.hutchinsmedia.com/

  4. avatar zestchannel

    <a href="http://www.sugarforspice.com/herre-nike-air-huarache-alle-lyser%C3%B8d-skol">herre nike air huarache alle lyser酶d</a> <a href="http://www.mybancorphsa.com/nike-air-max-bw-femminile-rosso-cielo-blu-scarpeh">nike air max bw femminile rosso cielo blu</a> <a href="http://www.scopatore.com/maschio-air-jordan-14-rosso-scarpei">maschio air jordan 14 rosso</a> <a href="http://www.galacticlogos.com/herren-nike-air-max-lunar-1-gold-wei%C3%9F-schuheh">herren nike air max lunar 1 gold wei脽</a> <a href="http://www.ispardmore.com/nike-lebron-xii-black-shoesl">nike lebron xii black</a> <a href="http://www.radioramabc.com/ottawa-senators-third-jersey-nfli">ottawa senators third jersey</a>
    zestchannel http://www.zestchannel.com/

  5. avatar davidmgaines

    <a href="http://www.tkbelazig.com/nike-air-max-2017-silver-green-shoesj">nike air max 2017 silver green</a> <a href="http://www.swiftgrocer.com/nike-lebron-ambassador-9-gul-hvid-skoq">nike lebron ambassador 9 gul hvid</a> <a href="http://www.warrnamboolag.com/air-jordan-retro-12-femmes-bleu-oranger-chaussuresl">air jordan retro 12 femmes bleu oranger</a> <a href="http://www.paineesq.com/hommes-asics-gel-hyper-33-violet-vert-chaussuresq">hommes asics gel hyper 33 violet vert</a> <a href="http://www.gemmatyndall.com/nike-air-max-tailwind-5-dam%C3%A4nner-silber-gelb-schuhei">nike air max tailwind 5 dam盲nner silber gelb</a> <a href="http://www.cestreaming.com/herren-asics-gel-lyte-5-blau-wei%C3%9F-schuheh">herren asics gel lyte 5 blau wei脽</a>
    [url=http://www.davidmgaines.com/]davidmgaines[/url]

  6. avatar prototipes

    <a href="http://www.isiklardamper.com/nike-air-huarache-dam%C3%A4nner-orange-gelb-obuvd">nike air huarache dam盲nner orange gelb</a> <a href="http://www.kalimias.com/nike-free-trainer-5.0-noir-volt-electro-violet-scarpec">nike free trainer 5.0 noir volt electro violet</a> <a href="http://www.leesgoedplus.com/nike-cortez-for-verkauf-australia-kurpesb">nike cortez for verkauf australia</a> <a href="http://www.ottomanplaza.com/nike-womens-frostee-rucker-black-name-number-logo-oakland-raiders-nfl-98-pullover-hoodie-nflr">nike womens frostee rucker black name number logo oakland raiders nfl 98 pullover hoodie</a> <a href="http://www.emazoncentral.com/air-jordan-14-hyper-lyser%C3%B8d-gul-butyg">air jordan 14 hyper lyser酶d gul</a> <a href="http://www.lieupekings.com/nike-warriors-9-andre-iguodala-white-nba-authentic-association-edition-jersey-nflz">nike warriors 9 andre iguodala white nba authentic association edition jersey</a>
    prototipes http://www.prototipes.com/

欢迎留言




用户登录

sitemap