当前位置:网站首页>Excel method is commonly used in text function 5
Excel method is commonly used in text function 5
2022-08-08 08:34:00 【The sensible meow~~】
As a data developer, in the process of work, although the database is used in most cases, the operation of Excel is unavoidable.The text() function has powerful functions. I will share more scenes encountered in my work below, enjoy~~
I. Meaning
Excel's text function is to format the content of the selected cell through the format code, and then display it in different display methods. It is a function that converts the content into text
Second, grammar
=TEXT (value, format code)
Parameter definition:
The first parameter value is the selected cell, which is the content that you want to format as text. The content of this cell can be numeric or characterString etc.
The second parameter is the target format you want to format.
III. Common usage scenarios
1, the value display is filled with 0
Format: =TEXT(cell,"000000")
Interpretation: Fill the content of the cell with 0 in front of the six digits, for example, 100 will be formatted as 000100
Extension: If you want to display two digits after the decimal point, the less than 2 digits are filled with 0s, and more than 2 digits are filled with 0.2-digit rounding, the formula is: =TEXT(cell, "00.00").For example, 21 is formatted as 21.00; 15.135 is formatted as 15.14.
2, extract date of birth based on ID number
Format: =TEXT(MID(cell,7,8),"00-00-00")
3, standard three-segment format for phone numbers
Format: =TEXT(cell,"000-0000-0000")
4, convert a numeric date to a specific date format
(1) 00/00/0000
Format: =TEXT(cell, "00/00/0000")
(2) 0000-00-00
Format: =TEXT(cell, "0000-00-00")
(3) full name of the day of the week
Format: =TEXT(TEXT(cell,"0000-00-00"),"aaaa")
(4) Abbreviated day of the week
Format: =TEXT(TEXT(cell,"0000-00-00"),"aaa")
(5) Year of extraction
Format: =TEXT(TEXT(cell, "0000-00-00"), "yyyy")
(6) Month of withdrawal
Format: =TEXT(TEXT(cell,"0000-00-00"),"mm")
(7) Pickup Day
Format: =TEXT(TEXT(cell,"0000-00-00"),"dd")
5, conditional section judgment
Condition section, you can choose the default, two, three, four sections, or can be customized
(1) Default condition section
Three sections are used more often, of course, you can also modify it to other sections according to your needs.
Format: [>0];[<0];[=0]
(2) Custom Condition Section
Format: [Condition 1];[Condition 2];[Other parts that do not meet the condition];[Text]
The above is the common way we use the text function in Excel, I am Meow~~, I hope everyone can communicate and learn together!
边栏推荐
- 数控机床工作平台位置伺服系统的的数学建模与仿真
- 【Enumeration】Continuous factor
- 各位大佬想问下, flinkcdc采集oracle我看了下延迟大概两分钟左右,想问下有啥解决方法吗
- shell循环语句
- 蔚来杯2022牛客暑期多校训练营6 ABGJM
- 面试官:工作中用过锁么?说说乐观锁和悲观锁的优劣势和使用场景
- Offensive and defensive world - ics-05
- golang-channel-一个基础channel并行操作的简单函数
- CesiumJS 更新日志 1.96 与 1.97 - 新构建工具 esbuild 体验及 Model API 更替完成
- 文件包含漏洞-知识点
猜你喜欢
随机推荐
Matlab实现异构交通流
推荐下载软件
ES8 | async and await
简单理解MVVM模型
DBeaver 22.1.4 released, a visual database management platform
看顶级测工怎么玩转Apifox接口测试工具
What exactly happens after entering the URL in the browser?
攻防世界——lottery
网安学习-应急响应2
Raspberry pie 】 【 without WIFI even under the condition of the computer screen
The industry's first "Causal Inference Whole Process" Challenge!WAIC 2022 · Hackathon invites global developer elites to challenge
方案 | 医疗单据OCR识别,为医保零星报销打造安全屏障
jupyter lab安装、配置教程
分清成员函数非成员函数和友元函数
DBeaver 22.1.4 发布,可视化数据库管理平台
idea big data tools 提交flink任务
【树莓派】在没有显示屏的情况下通过WIFI连电脑
设计圆类,求圆的周长
My MySQL installation that is how to solve
Redis读写分离(三)