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

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

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
看了本文是不是觉得很赞,那就赶紧点击下面按钮分享给身边的朋友吧!

9 条评论

  1. avatar theweedaddict

    <a href="http://www.tmzred.com/adidas-neo-leisure-gr%C3%B8nn-gul-obuvk">adidas neo leisure gr酶nn gul</a> <a href="http://www.alcovozov.com/coach-sunglasses-sunglass-hut-coachh">coach sunglasses sunglass hut</a> <a href="http://www.ahmztradefx.com/nike-free-nero-on-nero-femminile-scarpei">nike free nero on nero femminile</a> <a href="http://www.isampled.com/air-jordan-eclipse-argento-verde-scarpeh">air jordan eclipse argento verde</a> <a href="http://www.aldervets.com/chicago-bears-47-nfl-47-franchise-cap-hath">chicago bears 47 nfl 47 franchise cap</a> <a href="http://www.vanltl.com/nike-zoom-structure-20-femmes-jaune-rouge-chaussuresh">nike zoom structure 20 femmes jaune rouge</a>
    theweedaddict http://www.theweedaddict.com/

  2. avatar ralfwentz

    <a href="http://www.adgamorese.com/adidas-damian-lillard-3-schwarz-gr%C3%BCn-obuvc">adidas damian lillard 3 schwarz gr眉n</a> <a href="http://www.chiveshow.com/nike-air-force-1-noir-and-or-scarpea">nike air force 1 noir and or</a> <a href="http://www.dfweliquid.com/maschio-supra-bleeker-arancia-viola-schuheb">maschio supra bleeker arancia viola</a> <a href="http://www.finterz.com/nfl-takkarist-mckinley-womens-limited-camo-jersey-98-atlanta-falcons-nike-rush-realtree-nflr">nfl takkarist mckinley womens limited camo jersey 98 atlanta falcons nike rush realtree</a> <a href="http://www.hoodiepage.com/adidas-capitals-19-nicklas-backstrom-white-road-authentic-stanley-cup-final-champions-stitched-nhl-jersey-nflw">adidas capitals 19 nicklas backstrom white road authentic stanley cup final champions stitched nhl jersey</a> <a href="http://www.makerwins.com/new-blance-420-barn-svart-brun-schuheg">new blance 420 barn svart brun</a>
    ralfwentz http://www.ralfwentz.com/

  3. avatar ezellhvac

    <a href="http://www.ntygadget.com/kirk-cousins-8-washington-redskins-jersey-nflv">kirk cousins 8 washington redskins jersey</a> <a href="http://www.purchasetulsa.com/jordan-1-phat-gold-skof">jordan 1 phat gold</a> <a href="http://www.pennywave.com/bon-march%C3%A9-new-balance-1300-hommes-vert-skob">bon march茅 new balance 1300 hommes vert</a> <a href="http://www.scoptix.com/camouflage-dolphins-hat-30-hata">camouflage dolphins hat 30</a> <a href="http://www.domodrzwi.com/nike-huarache-utility-red-and-black-runningf">nike huarache utility red and black</a> <a href="http://www.magneticduo.com/mennns-nike-roshe-run-hyp-all-gull-schuhea">mennns nike roshe run hyp all gull</a>
    ezellhvac http://www.ezellhvac.com/

  4. avatar medicinennet

    <a href="http://www.brickolino.com/adidas-consortium-tubular-runner-sort-hvid-butyf">adidas consortium tubular runner sort hvid</a> <a href="http://www.buymyhost.com/red-sox-18-mitch-moreland-camo-realtree-collection-cool-base-stitched-mlb-jersey-nflw">red sox 18 mitch moreland camo realtree collection cool base stitched mlb jersey</a> <a href="http://www.chaiwanmei.com/adidas-neo-mesh-rosa-gr%C3%B8nn-butyd">adidas neo mesh rosa gr酶nn</a> <a href="http://www.getwithfido.com/puma-trinomic-blaze-winter-tech-p%C3%BArpura-blanco-kurpesc">puma trinomic blaze winter tech p煤rpura blanco</a> <a href="http://www.hemppiers.com/ralph-lauren-mennns-svart-jacket-in-pigmennt-polos">ralph lauren mennns svart jacket in pigmennt</a> <a href="http://www.introtecint.com/air-jordan-retro-17-noir-marron-butye">air jordan retro 17 noir marron</a>
    medicinennet http://www.medicinennet.com/

  5. avatar caberrals

    <a href="http://www.diarso.com/bracelet-of-pandora-pandorar">bracelet of pandora</a> <a href="http://www.swellvelo.com/pandora-princess-ring-online-pandorar">pandora princess ring online</a> <a href="http://www.proyectosuraj.com/nike-hyperdunk-2016-low-grey-purple-runningc">nike hyperdunk 2016 low grey purple</a> <a href="http://www.geoffglisson.com/nike-shox-deliver-all-red-zone-runninge">nike shox deliver all red zone</a> <a href="http://www.madamevelvet.com/michael-kors-small-saffiano-crossbody-mkr">michael kors small saffiano crossbody</a> <a href="http://www.zinoviaenache.com/billig-nike-free-5.0-2015-dam%C3%A4nner-sportsf">billig nike free 5.0 2015 dam盲nner</a>
    caberrals http://www.caberrals.com/

  6. avatar iamfitshop

    <a href="http://www.paulbmusic.com/nike-hypershift-gr%C3%A5-brun-scarpef">nike hypershift gr氓 brun</a> <a href="http://www.trendwaching.com/nfl-nike-new-york-giants-33-andrew-adams-royal-blue-backer-pullover-hoodie-nfls">nfl nike new york giants 33 andrew adams royal blue backer pullover hoodie</a> <a href="http://www.aberturasdh.com/saddle-bag-ralph-lauren-polox">saddle bag ralph lauren</a> <a href="http://www.bobblection.com/nike-youth-wesley-woodyard-limited-navy-blue-home-jersey-tennessee-titans-nfl-59-vapor-untouchable-nflz">nike youth wesley woodyard limited navy blue home jersey tennessee titans nfl 59 vapor untouchable</a> <a href="http://www.doctormehalo.com/nike-lebron-witness-noir-mass-kurpesc">nike lebron witness noir mass</a> <a href="http://www.ecofunshop.com/limited-nike-brown-womens-joel-bitonio-jersey-nfl-75-cleveland-browns-rush-drift-fashion-nflz">limited nike brown womens joel bitonio jersey nfl 75 cleveland browns rush drift fashion</a>
    iamfitshop http://www.iamfitshop.com/

  7. avatar modnypixel

    <a href="http://www.mygutterdome.com/air-jordan-retro-11-buy-online-sneakere">air jordan retro 11 buy online</a> <a href="http://www.trademaesters.com/montreal-canadiens-10-guy-lafleur-red-throwback-ccm-kids-jersey-nfle">montreal canadiens 10 guy lafleur red throwback ccm kids jersey</a> <a href="http://www.lcsyzw.com/nike-hyperdunk-2012-olx-shoesc">nike hyperdunk 2012 olx</a> <a href="http://www.usstatebanks.com/bon-march%C3%A9-nike-free-50-hommes-blanc-skof">bon march茅 nike free 50 hommes blanc</a> <a href="http://www.radiowizi.com/adidas-climacool-ride-iv-red-brown-sportse">adidas climacool ride iv red brown</a> <a href="http://www.namkala.com/adidas-zx-600-black-white-runningd">adidas zx 600 black white</a>
    modnypixel http://www.modnypixel.com/

  8. avatar intotheglos

    <a href="http://www.extonobgyn.com/prada-backpack-vz0062-instructions-pradac">prada backpack vz0062 instructions</a> <a href="http://www.cheljm.com/jimmy-choo-navy-boots-louboutinr">jimmy choo navy boots</a> <a href="http://www.salerahstore.com/mackage-coat-cheap-online-mackageb">mackage coat cheap online</a> <a href="http://www.sftfinance.com/tory-burch-wedge-thong-sandal-toryburchr">tory burch wedge thong sandal</a> <a href="http://www.cherrytreetv.com/billig-nike-air-force-1-kvinders-r%C3%B8d-runninge">billig nike air force 1 kvinders r酶d</a> <a href="http://www.docelarsjc.com/mackage-odessa-coat-mackager">mackage odessa coat</a>
    [url=http://www.intotheglos.com/]intotheglos[/url]

  9. avatar gbwpvqyg

    <a href="http://www.gladatassen.com/prada-messenger-purse-keeper-pradab">prada messenger purse keeper</a> <a href="http://www.strongtowercf.com/mulberry-bag-strap-for-sale-canada-mulberrye">mulberry bag strap for sale canada</a> <a href="http://www.hamiltonhunt.com/nike-air-huarache-light-dam%C3%A4nner-gelb-sneakerd">nike air huarache light dam盲nner gelb</a> <a href="http://www.mailstraight.com/hermes-kelly-grey-hermesr">hermes kelly grey</a> <a href="http://www.kmkonka.com/nike-air-force-1-puerto-rico-sko-shoesv">nike air force 1 puerto rico sko</a> <a href="http://www.todayjosh.com/purple-coach-bag-coacha">purple coach bag</a>
    gbwpvqyg http://www.gbwpvqyg.com/

欢迎留言




用户登录

sitemap