Buzz
When working in Excel with character strings, there are numerous instances where you need to split them to effectively use the data. Manually splitting requires a significant amount of time and effort. This article provides guidance on how to split strings in Excel.
Functions used for splitting character strings:
- Left: This function retrieves the substring located on the left side of the original string.
- Right: Function to extract the substring located on the right side of the original string.
- Mid: Function to extract the substring located in the middle of the original string.
Syntax:
- LEFT(text,[num_char])
- RIGHT(text,[num_char])
- MID(text,start_num, num_char)
In which:
+ text: Original character string containing the substring to be extracted
+ num_char: the number of characters to be extracted.
+ start_num: the position to start extracting the substring from.
To split a character string, there are many methods:
- Utilize the left, right, and mid string cutting functions combined with the replace feature in Excel.
Utilize Excel functions like left, right, mid, etc. along with some search functions to determine positions accurately.
In the following examples, detailed guidance is provided on how to extract strings in Excel, which is widely used in practical scenarios today.
Example 1: Separate surname and given name into 3 distinct fields: surname, middle name, and given name
Step 1: Copy the entire last name and first name data into the first name column:
Step 2: Select all data in the Name column -> press the Ctrl + H key combination and enter the following content:
- In the Find what: section, input the character “*” and a space.
- In the Replace with: section, leave it blank.
Press Replace all to replace all characters before the space with a blank character:
Step 3: A confirmation dialog box will appear, select OK:
The result has been obtained from the last name and first name column:
Step 4: Retrieve the last name and first name value from the last name and first name column by entering the formula: = LEFT(C6,LEN(C6)-LEN(G6)-1)
Press Enter to get the result:
Step 5: Copy the entire last name column just obtained and paste it into the middle name column. Note that during the paste process into the middle name column, right-click and select paste as value as shown in the image:
Step 6: Perform similar steps as for the first name column to retrieve the middle name value using the replace feature in Excel:
The result obtained the middle name value from the last name column:
Step 7: Retrieve the value for the last name column.
Example 2: Splitting Character Strings into Product Name and Origin.
- In this scenario, when entering materials, the character string is divided into two parts: the product name and the origin, separated by a hyphen. To perform the split, use the Left and Right functions. However, because each material has a different name and length, the number of characters needed in the Left and Right functions is unknown in advance:
- Since all entered material values consist of the product name and origin separated by a hyphen, use the hyphen to determine the number of characters needed for the Left and Right functions by using the Find function.
The process of splitting the product name and product origin involves the following steps:
Step 1: In the cell where you want to retrieve the product name, enter the formula: =LEFT(C6,FIND('-',C6)-1)
Step 2: Press Enter to obtain the product name from the entered material:
Step 3: Copy the formula to get results for remaining values:
Step 4: In the cell where you want to retrieve the origin of the material, enter the formula: =RIGHT(C6,LEN(C6)-FIND('-',C6))
Step 5: Press Enter -> copy the formula to get results for remaining values:
With these simple steps, you can separate the product name and origin from the entered material code:
Above is a detailed guide on how to split strings in Excel based on specific examples. Wish you success!
