To return a string with the last name and current age, use the following formula: Create a column in which the surname of the employee and his age in the format “Jon - 27” will be displayed. The table contains information about employees in the columns name and date of birth. For simplicity, instead of searching for the last position number (for example, using the LEN function), the number 100 is indicated, which in this example is guaranteed to exceed the number of characters in the original line.Īs a result of the calculations we get: How to calculate age by date of birth in Excel?Įxample 3. The final value is used as an argument to the Start_num of the MID function. A unit is added to the received number to find the position of the first character of the brand name of the product. To fill the “Mark” column, use the following array formula:įIND function returns the position of the space character. To fill the column "Name" we use the following formula:įIND function returns the position number of the space character "" in the line being scanned, which is taken as an argument to the number_ characters of the MID function. Split the existing lines into substrings with the name and brand, respectively, and write the values in the corresponding columns of the table. A text column with the name and brand of goods is stored in a table column. It was possible to divide each date separately into several cells by columns: day, month, and year.Įxample 2. Thus, we were able to cut the text into cells of column A. In a similar way, we select the month numbers and years to fill in the corresponding columns, taking into account that the month number starts with the 4th character in each row and the year from the 7th. 2 - the number of the last position of the character of the extracted substring.1 - the number of the initial position of the character of the extracted substring (the first character in the original string).A2:A10 - a range of cells with a textual representation of the dates from which the numbers of the days will be highlighted.To fill in the day number, use the following formula: Record separately in adjacent columns the number of the day, month and year, isolated from the presented dates.
The table column contains dates recorded as text strings. How to divide the text into several cells in columns in Excel?Įxample 1. The two-byte languages are Korean, Japanese, and Chinese. If a two-byte language is used on the computer, each character will be counted as two characters when using MIDB. When working with such languages, the results of the functions MID and MIDB (returns a substring from a string based on the number of specified bytes) do not differ. One character in single-byte-encoded languages corresponds to 1 byte. Examples of using the MID function in Excel
0 kommentar(er)
