Excel function application text/date/time functions

Text Functions are functions that can process text strings in formulas. For example, you can change the case sensitivity or determine the length of a text string; you can replace or remove some characters. The date and time functions can analyze and process the date and time values in the formula. For the list of these two types of functions, see the appendix. Here we will only briefly introduce some common functions.

I. Text Functions

(1) case-insensitive Conversion

Lower -- converts all uppercase letters in a string to lowercase letters.

Upper -- converts text into a large write format.

Proper -- converts the first letter of a string and the first letter after any non-letter character into uppercase. Convert other letters to lowercase letters.

The basic syntax of these three functions is the function name (text ). Example:

The existing string is: please come here! We can see that this sentence is case-insensitive due to nonstandard input.

The above three functions can be used to convert the text display style to make the text standardized. See Figure 1

Lower (please come here !) = Please come here!

Upper (please come here !) = Please come here!

Proper (please come here !) = Please come here!

　
Figure 1

(2) extract some characters in the string

Excel function application review

● Logical functions of Excel Functions ● Mathematical and trigonometric functions used in Excel Functions ● EXCEL function application function Introduction

You can use mid, left, right, and other functions to obtain a part of characters from a long string. The syntax format is

Left function: Left (text, num_chars) where text is a string containing the characters to be extracted. Num_chars specifies the number of characters to be extracted from left.

Mid function: Mid (text, start_num, num_chars). Text is the text string containing the characters to be extracted. Start_num is the position of the first character to be extracted in the text.

Right function: Right (text, num_chars) where text is a string containing the characters to be extracted. Num_chars specifies the number of characters to be extracted by right.

For example, the specific function for retrieving the characters "this", "apple", and "is" from the string "this is an apple." is written as follows.

Left ("this is an apple", 4) = This

Right ("this is an apple", 5) = Apple

Mid ("this is an apple", 6, 2) = is

　
Figure 2

(3) Remove the blank string

In string form, white space is also a valid character. However, if there is a blank character in the string, it is easy to make an error when judging or comparing data, in Excel, you can use the trim function to clear white spaces in strings.

Syntax format: trim (text) where text is the text in which spaces need to be cleared.

Note that the trim function does not clear a single space between words. If you need to clear this space, we recommend that you use the replacement function. For example, the function for clearing spaces from the string "my name is Mary" is written as: trim ("My name is Mary") = My name is Mary. See figure 3.

　
Figure 3

(4) string comparison

You can use the exact function to compare different strings in the data table. This function tests whether the two strings are identical. If they are identical, true is returned; otherwise, false is returned. The function exact can be case sensitive, but the format difference is ignored. Use the function exact to test the text in the input document. Syntax format: exact (text1, text2) text1 is the first string to be compared. Text2 is the second string to be compared. Example: see Figure 4

Exact ("China", "China") = false

　
Figure 4

Ii. Date and Time Functions

In the process of data table processing, the date and time functions are very important processing basis. Excel also provides a wealth of functions for you to use.

(1) retrieve the current system time/date information

Functions used to retrieve the current system time/date information include now and today.

The syntax format is the function name ().

(2) obtain partial Field Values of date/time

If you need data of a separate year, month, number of days, or hour, you can use the hour, day, month, and year functions to retrieve the required data from the date/time. For more information, see Figure 5.

For example, if you want to return the year, month, number of days, and hours of, you can use the corresponding functions.

Year (E5) = 2001

Month (E5) = 5

Day (E5) = 30

Hour (E5) = 12

　
Figure 5

For more useful date/time functions, see the appendix. The following is a specific example to illustrate the use of the Excel text function and date function.

Iii. Example: Make a concise and elegant analysis table of personnel data

1. Example

In the Personnel Data Table of a company shown in figure 6, all items except the serial number, employee name, ID card number, and participation time are calculated by function compute.

　
Figure 6

In this example, we will explain in detail how to use the function to find:

(1) automatically extract the date of birth and gender information from the ID card number.

(2) The length of service information is automatically extracted from the time of participation.

2. ID card number related knowledge

Before learning how to automatically extract the date of birth and gender information from the ID card number, you must first understand the meaning of the ID card number. We know that today's ID card numbers have 15/18 digits. The ID card number issued earlier is 15 digits. Now the issued ID card has 18 digits due to the extension of the Year (changed from two digits to four digits) and the end verification code. These two ID card numbers will coexist for a long period of time. The meanings of the two ID card numbers are as follows:

(1) 15-digit ID card numbers: 1 ~ 6-bit is the region code, 7 ~ 8 digits are the Year of birth (2 digits), 9 ~ The 10 digits are the month of birth, 11 ~ 12 digits are the date of birth, 13th ~ The 15-digit sequence number indicates gender. The odd number indicates male and the even number indicates female.

(2) 18-digit ID card number: 1 ~ 6-bit is the region code, 7 ~ The 10 digits are the Year of birth (4 digits), 11 ~ 12 digits are the month of birth, 13 ~ 14 digits are the date of birth, 15th ~ The 17-digit sequence number indicates the gender, the odd number indicates the male, and the even number indicates the female. The 18-digit verification digit is used.

3. Application functions

In this example, the following Excel functions are applied to achieve automatic data extraction.

(1) If function: return the corresponding value based on the test result of the logical expression. The IF function allows nesting.

Syntax format: If (logical_test, value_if_true, value_if_false)

(2) concatenate: combines several text items into one text item.

Syntax format: concatenate (text1, text2 ......)

(3) mid: returns the specified length of characters starting from the starting position specified in the text string.

Syntax: Mid (text, start_num, num_chars)

(4) Today: returns the current date in the computer system.

Syntax format: Today ()

(5) datedif: calculate the number of days, number of months, or number of years between two dates.

Syntax format: datedif (start_date, end_date, Unit)

(6) value: converts a string representing a number into a number.

Syntax format: Value (text)

(7) Right: return the last or multiple characters in the text string based on the specified number of characters.

Syntax: Right (text, num_chars)

(8) int: return the integer after real number rounding. Syntax: int (number)

4. Formula writing and explanation (using employee Andy as an example)

Note: to avoid too many nesting in the formula, the ID number here is limited to 15 characters. If you understand the formula, you can simply modify it to apply to the 18-digit ID card number, or even to the coexistence of 15 and 18.

(1) Gender Based on ID card numbers

= If (value (right (E4, 3)/2 = int (value (right (E4, 3)/2), "female", "male ")

Formula explanation: A. Right (E4, 3) is used to find the representative numbers in the ID card number. What is actually obtained is the string representing the numbers.

B. Value (right (E4, 3) is used to convert the string representing the number obtained in the previous step to a number.

C. value (right (E4, 3)/2 = int (value (right (E4, 3)/2 is used to determine whether the ID number is an odd or even number, you can also use the MOD function to make judgments.

D. = If (value (right (E4, 3)/2 = int (value (right (E4, 3)/2), "female", "male ") and if the formula above determines that the number is an even number, "female" is displayed. Otherwise, if the number is an odd number, "male" is returned ".

(2) Calculate the date of birth based on the ID card number

= Concatenate ("19", mid (E4, 7,2), "/", mid (E4, 9,2), "/", mid (E4, 11,2 ))

Formula explanation: A. mid (E4,) is a string used to obtain the number representing the year in the ID card number.

B. Mid (E4, 9,2) is the string used to obtain the number representing the month in the ID card number.

C. mid (E4, 11,2) is a string used to obtain the number representing the date in the ID card number.

D. concatenate ("19", mid (E4, 7,2), "/", mid (E4, 9,2), "/", mid (E4, 11,2 )) the purpose is to combine multiple strings for display.

(3) Obtaining seniority (that is, length of service) based on the time of participation)

= Concatenate (datedif (F4, today (), "Y"), "year", datedif (F4, today (), "ym"), "months ")

Formula explanation:

A. Today () is used to find the current time of the system.

B. datedif (F4, today (), "Y") is used to calculate the year of the difference between the current system time and the participation time.

C. datedif (F4, today (), "ym") is used to calculate the month of the difference between the current system time and the participation time, ignoring the day and year in the date.

D. = concatenate (datedif (F4, today (), "Y"), "year", datedif (F4, today (), "ym"), "months ") the purpose is to combine multiple strings for display.

5. Other Instructions

In this personnel data table, we also found that the creation date is and displayed in the same cell. How is this implemented? Is it manually added? No. Actually, this date is changed. It displays the current system time. The today function and the text function are used to create a message that contains the current date and represents the date in "DD-mm-YYYY" format.

The formula is written as follows: = "creation date:" & text (today (), "DD-mm-YYYY ")

Now, we have a general understanding of text functions, date and time functions, and some application fields. I believe that with the continuous research in this area, it will be more widely used.

Appendix 1: Text Functions

Function Name	Function Description	Syntax
ASC	Change the full-width (double-byte) English letters in the string to half-width (single-byte) characters.	ASC (text)
Char	Returns the character corresponding to the numeric code. The function char can convert the code in other types of computer files into characters.	Char (number)
Clean	Delete characters that cannot be printed in text. Using the clean function for strings entered from other applications will delete the characters that cannot be printed by the current operating system. For example, you can delete low-level computer code that usually appears in the header or tail of a data file and cannot be printed.	Clean (text)
Code	Returns the numerical code of the first character in a text string. The Returned Code corresponds to the character set currently used by the computer.	Code (text)
Concatenate	Merge several text strings into one text string.	Concatenate (text1, text2 ,...)
Dollar	Rounds the decimal number to the specified number of digits according to the currency format and converts it to text.	Dollar or RMB (number, decimals)
Exact	This function tests whether the two strings are identical. If they are identical, true is returned; otherwise, false is returned. The function exact can be case sensitive, but the format difference is ignored. Use the function exact to test the text in the input document.	Exact (text1, text2)
Find	Find is used to find the find_text in other text strings (within_text), and returns the start position number of find_text starting from the first character of within_text.	Find (find_text, within_text, start_num)
Fixed	Rounds the number of decimal places by a specified number of decimal places. The period and comma are used to set the format of the number in decimal format and return the result in the form of a text string.	Fixed (number, decimals, no_commas)
JIS	Change the half-width (single-byte) English letters or katakana to full-width (double-byte) characters.	JIS (text)
Left	Left returns the first or first few characters in a text string based on the specified number of characters. Leftb returns the first or first few characters in a text string based on the specified number of bytes. This function is used for double-byte characters.	Left (text, num_chars) Leftb (text, num_bytes)
Len	Len returns the number of characters in a text string. Lenb returns the number of bytes used to represent characters in a text string. This function is used for double-byte characters.	Len (text) Lenb (text)
Lower	Converts all uppercase letters in a string to lowercase letters.	Lower (text)
Mid	Mid Returns the number of characters starting from the specified position in the text string. The number is specified by the user. The number of characters in the text string returned by midb starting from the specified position. The number is specified by the user. This function is used for double-byte characters.	Mid (text, start_num, num_chars) Midb (text, start_num, num_bytes)
Phonetic	Extract the pinyin (furigana) characters from the text string.	Phonetic (reference)
Proper	Converts the first letter of a string and the first letter after any non-letter character into a uppercase letter. Convert other letters to lowercase letters.	Proper (text)
Replace	Replace uses other text strings and replaces some text in a text string with the specified number of characters. Replaceb uses other text strings and replaces some text in a text string with the specified number of characters. This function is designed for dubyte characters.	Replace (old_text, start_num, num_chars, new_text) Replaceb (old_text, start_num, num_bytes, new_text)
Rept	The text is repeatedly displayed based on the given number of times. You can use the rept function to repeatedly display a text string and fill the cell.	Rept (text, number_times)
Right	Right returns the last or multiple characters in the text string based on the specified number of characters. Rightb returns the last or multiple characters in the text string based on the specified number of characters. This function is used for double-byte characters.	Right (text, num_chars) Rightb (text, num_bytes)
Search	Search returns the number of a specific character at the position of a specific character or text string for the first time starting from start_num. Use search to determine the position of a character or text string in another text string, so that you can use the mid or replace function to change the text. Searchb can also find the find_text in other text strings (within_text) and return the start position number of find_text. The result is based on the number of bytes used by each character and starts from start_num. This function is used for double-byte characters. In addition, you can use findb to search for text strings in other text strings.	Search (find_text, within_text, start_num) Searchb (find_text, within_text, start_num)
Substitute	Replace old_text with new_text in the text string. To replace the specified text in a string, use the Substitute function. To replace any text at the specified position in a string, use the replace function.	Substitute (text, old_text, new_text, instance_num)
T	Converts a value to a text value.	T (value)
Text	Converts a value to text in the specified numeric format.	Text (value, format_text)
Trim	In addition to a single space between words, clear all spaces in the text. You can use the trim function to retrieve text with irregular spaces from other applications.	Trim (text)
Upper	Converts text into a large write format.	Upper (text)
Value	Converts a string representing a number to a number.	Value (text)
Widechar	Converts a single-byte character to a double-byte character.	Widechar (text)
Yen	Convert the number into text in the format of $ (YEN) and round the number at the specified position.	Yen (number, decimals)

Appendix 2. Date and Time Functions

Function Name	Function Description	Syntax
Date	Returns the number of series representing a specific date.	Date (year, month, day)
Datedif	Calculates the number of days, months, or years between two dates.	Datedif (start_date, end_date, Unit)
Datevalue	The main function of the datevalue function is to convert a date in text into a series number.	Datevalue (date_text)
Day	Returns the number of days of a date in series, represented by an integer of 1 to 31.	Day (serial_number)
Days360	Returns the number of days in a two-day period based on the algorithm of 360 days per year (30 days per month, 12 months in total per year.	Days360 (start_date, end_date, method)
Edate	Returns the number of date series specified before or after the specified date (start_date. You can use the edate function to calculate the date of the expiration date on the same day as the release date on January 1, January.	Edate (start_date, months)
Eomonth	Returns the number of series of the last day of the specified month before or after start-date. The eomonth function is used to calculate the number of time series of the last day of a specific month. It is used to calculate the expiration date of securities.	Eomonth (start_date, months)
Hour	Returns the hour of the time value. It is an integer between 0 (a.m.) and 23 (p.m.	Hour (serial_number)
Minute	Returns the minute in the time value. It is an integer between 0 and 59.	Minute (serial_number)
Month	Returns the month of the date in series. The month is an integer between 1 (January) and 12 (December.	Month (serial_number)
Networkdays	Returns the complete workday value between start-data and end-data. Workdays do not include weekends and specified holidays	Networkdays (start_date, end_date, holidays)
Now	Returns the number of series corresponding to the current date and time.	Now ()
Second	Returns the number of seconds of the time value. The returned number of seconds is an integer between 0 and 59.	Second (serial_number)
Time	Returns a small value of a specific time. The small value returned by the function time is a value ranging from 0 to 0.99999999, representing from 0:00:00 (12:00:00 A.M) to 23:59:59 (11:59:59 p. m.	Time (hour, minute, second)
Timevalue	Returns a small value of time represented by a text string. This small value is a value ranging from 0 to 0.999999999, representing the time from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 PM.	Timevalue (time_text)
Today	Returns the number of series of the current date, which is the date-time code used by Microsoft Excel for date and time calculation.	Today ()
Weekday	Returns the day of a week. By default, the value is an integer between 1 (Sunday) and 7 (Saturday.	Weekday (serial_number, return_type)
Weeknum	Returns a number representing the week of the year.	Weeknum (serial_num, return_type)
Workday	Returns the date value of a date before or after a specified business day. Workdays do not include weekends and specified holidays.	Workday (start_date, days, holidays)
Year	Returns the year of a date. The return value is an integer between 1900 and 9999.	Year (serial_number)
Yearfrac	Returns the percentage of days between start_date and end_date in the year.	Yearfrac (start_date, end_date, basis)