Office hacks: Text function with live data transformation more efficient

Work often need to sort data, filtering, summation and other operations, but some data display is not standardized, does not meet the requirements, will affect the smooth implementation of the above operations. Live use of Text function, you can quickly format the data in accordance with the actual needs of the transformation, to meet their needs in the form of display.

1. date format transformation

Excel by default uses a date format similar to 2020-07-01, 2020/07/01, but this format can fail when performing filtering operations. For example, the following table is the statistics of a product sales in July, now you need to filter by date to find out the details of all Saturday and Sunday sales in the month (Figure 1).


As the default date format does not show the day of the week, we can not directly according to the date of Saturday, Sunday filter, then you can use the Text function for the date format conversion. Insert a new column after the A column, then enter the formula “= TEXT (A3, “aaaa”)” in B2 (that the text in A3 will be converted to “aaaa” format, that is, Excel’s default Chinese week format), the drop-down fill can display the week. If you want to convert to English week form, you can enter “= TEXT (A3, “dddd”)”. Now select column B click “Data → Filter”, in the open filter window check the “Saturday”, “Sunday” option, and then enter the formula in C35 “= SUBTOTAL (109, C3: C34)” (109 means that only the sum of the filtered data, ignoring the hidden data), so you can find the sum of weekend sales (Figure 2).


Text function can also be used to convert non-standard date data to a standard format. For example, many friends will use a similar “202701”, “20200702” format to enter the date, this non-standard date in Excel will be identified as a number, can not participate in the normal operation. Now just insert a column after the original data column, enter the formula “= TEXT (A3, “0 year 00 month 00 date”)” in B2, drop down can be changed to a standard form similar to “2020 July 01”, after the same can Use the above function to display the week and other information (Figure 3).


Formula explanation.

The 0 is a placeholder that divides the 8-digit number into three segments using the year-month-day format. The date format is divided sequentially from right to left, with the rightmost 2 digits being the day, the middle 2 digits being the month, and the leftmost 4 digits being the year.

If you want to convert a standard date such as “July 01, 2020” to the form of “20200701” (for example, many companies’ database systems use such an 8-digit format for dates, so that the Excel-generated (so to import the data generated by Excel into the database will have to change the format). As above, enter the formula “= TEXT (A2, “yyyymmdd”)” in B2 (indicating that the date of A2 in accordance with the year yyyy, month mm, day dd form of combination), the formula can be changed to 8-digit format after the drop-down (Figure 4).


Tip: If you want to convert the format into the actual cell display numbers such as 20200701 (rather than the formula), you can select the converted numbers to copy, and then select “Paste → Selective Paste → Value” can be.

2. Time Format Transformation

In attendance statistics, it is often necessary to do calculations on time, for example, to calculate employees’ overtime time, it is necessary to do statistics by hour. However, Excel’s default time statistics over 24 hours will be automatically rounded to days, so the statistics are inaccurate, because overtime is calculated by multiplying the total hours by the unit price (Figure 5).


With the help of the Text function you can convert days to hours. Locate C2 and enter the formula “=TEXT(SUM(B2:B13),”[h]:mm:ss”)” (indicating that the summed value is displayed in the format of hours, minutes and seconds), and then drop down so that it will not be rounded by day, but directly displayed as total overtime hours (Figure 6).


3. Digital Format Transformation

Finance staff often have to convert the figures to case, such as the amount of the total to fill in the amount of capitalization. If you want to change the ordinary figures to uppercase, you can also use the Text function to convert. For example, in the figure above, you need to calculate the total overtime (assuming that the hourly overtime rate is $4.33), and the use of uppercase figures to represent, simply locate B16 and enter the formula “= B15 * 4.33 * 24” (because B15 is the time format, it and 24 will be multiplied to show the actual hourly figures), so that you get The overtime rate is $225.16. Continue to enter the formula “=TEXT(B16*100,”[DBnum2]0佰0拾0圆0角0分”)” in B17 (because there is no decimal point in the financial upper case numbers, here B16*100 becomes an integer, and [DBnum2] indicates that the data is defined using the form of hundred rounded corners and minutes), so that It will be possible to display the overtime in accordance with the financial rounded points (Figure 7).


4. convert the operation result to text display

In some statistical reporting, text descriptions can be more intuitive representation of the final results, such as the following table directly show a profit of $ XX, a loss of $ XX or capital preservation. This type of text display can also be converted using the Text function, in C2, enter the formula “= TEXT (B2-A2, “profit $ 0.00; loss – $ 0.00; capital preservation”)” (here the results of the operation of B2-A2 in the form of profit, loss, capital preservation display), pull down the formula, you can display the visual result (Figure 8).


Leave a Comment