As shown in the figure below, if there is extra data after the string and you want to extract only the string to the left of the extra data, you cannot simply use the LEFT function alone because the number of characters is not constant.
."A2The cell's "$20Function to extract the left side from the "+" mark
=LEFT(A2,FIND("\",A2)-1)In the following sections, we will explain the concept of this function.
To extract the characters before a specific character, use theHow to extract after a specific character "RIGHT function and FIND function" Excel (Excel).
How to use the LEFT function
The LEFT function is a function that extracts ____ characters from the right side of a cell string.
=LEFT(string,[number of characters])
Both full-width and half-width characters are counted as one character.
This time, in the state shown in the above figure, the "A2cell to the left of the6To extract a string of characters, the "string" must be "A2cell, the "number of characters" is6characters, so enter the following
=LEFT(A2,6)Then, as shown in the figure below, we were able to display strings other than monetary amounts.
However, if this function is applied to the cells below as well, only six characters are extracted, as shown in the figure below, so only a portion of the amount is displayed, or the entire text is not displayed.
This is because the string in each cell is not necessarily 6 characters.
If the location of the character you want to extract moves from cell to cell, you can use the "FIND function" to solve this problem.
How to use the FIND function
The FIND function returns an integer that indicates how many characters of a particular string are present in a cell.
=FIND(search string,target,[start position])
Both full-width and half-width characters are counted as one character.
The "³" symbol is replaced by "A2To find out how many characters are in the "∗" column, enter the function as shown below.
=FIND("\", "}", "}", "}")A2)The number "7" was returned as shown in the figure below.
This is called "$20This is because the "+" mark is the "seventh letter" counting from the left.
To reflect this in the LEFT function, the function is described as follows.
=LEFT(A2,FIND("\",A2))Then you can see the following "$20I even saw the "" mark.
This is a$20This is because the "" mark is the seventh character and the seventh character from the left is displayed.
To avoid this, the FIND function is followed by "-1I will try to describe "the
=LEFT(A2,FIND("\",A2))-1)When this is reflected from "B2" cell to "B5" cell, it can be displayed correctly according to the number of characters as shown in the figure below.
Conclusion
In this article, we explained how to extract the left side from a specific character, but if you want to extract the right side from a specific character, you can use theHow to extract after a specific character "RIGHT function and FIND function" Excel (Excel)article for more information.
The usage of the LEFT function isExcel (Excel) RIGHT and LEFT functions to extract a specific number of characters from the right or left of a stringIt is also explained in
Comment