# LEFTB Function in Excel

📌 Part 1: Introduce

The LEFTB function in Microsoft Excel allows you to extract a specified number of bytes from the beginning of a text string. It is beneficial when working with double-byte character sets (DBCS) in languages like Chinese, Japanese, or Korean, where characters are represented by two bytes.

Purpose: The LEFTB function extracts a portion of a text string from the left side based on the specified number of bytes.

Syntax & Arguments:

syntax
```LEFTB(text, num_bytes) ```
• `text`: This is the text string from which you want to extract the left portion of bytes.
• `num_bytes`: This is the number of bytes you want to remove from the left side of the text string.

Return value: The LEFTB function returns a text string containing the specified number of bytes from the left side of the original text.

Remarks:

• The LEFTB function counts each character as two bytes in a DBCS language but counts each character as one byte in a single-byte character set (SBCS) language.
• If the `num_bytes` argument exceeds the number of bytes in the text string. The entire text string will be returned.

📋 Part 2: Examples

Example 1: Extracting the Leftmost 3 Bytes Suppose you have a list of text values in column A and want to extract the leftmost 3 bytes from each text value.

AB
1Text ValueLeft 3 Bytes
2你好世界=LEFTB(A2, 3)
3こんにちは
4안녕하세요
• In this example, the formula `=LEFTB(A2, 3)` cell B2 extracts the leftmost 3 bytes from the text value in cell A2.
• The result in cell B2 will be “你好” as it extracts the first 3 bytes from the text value “你好世界”.

Example 2: Handling Single-Byte Characters In this example, we will demonstrate how the LEFTB function handles single-byte characters in a DBCS language.

AB
1Text ValueLeft 5 Bytes
2你好ABC=LEFTB(A2, 5)
3こんにちは
4안녕하세요
• In this example, the formula `=LEFTB(A2, 5)` cell B2 extracts the leftmost 5 bytes from the text value in cell A2.
• The result in cell B2 will be “你好A” as it extracts the first 5 bytes, including the single-byte characters “A” and “B”.

Example 3: Extracting Entire Text String Sometimes, you may want to extract the entire text string, regardless of length. In such cases, you can use the LEFTB function without specifying the number of bytes.

AB
1Text ValueEntire Text
2你好世界=LEFTB(A2)
3こんにちは
4안녕하세요
• In this example, the formula `=LEFTB(A2)` in cell B2 extracts the entire text value from cell A2.
• The result in cell B2 will be “你好世界” as it extracts the entire text string.

Example 4: Extracting Leftmost Characters with Conditional Formatting Suppose you have a list of customer names in column A, and you want to apply conditional formatting to highlight the first two characters of each name. Here’s how you can do it:

AB
1Customer NameHighlighted Name
2John Doe=LEFTB(A2, 2)
3Jane Smith
4Mark Johnson
• Select cell B2 and apply conditional formatting to highlight the cell based on a formula.
• Use the formula `=LEFTB(\$A2,2)` as the conditional formatting rule.
• This will highlight the first two characters of each customer’s name.

Example 5: Extracting Initials from Full Names Suppose you have a list of employees’ full names in column A and want to extract their initials. Here’s how you can do it:

AB
1Full NameInitials
2John Doe=LEFTB(A2, 1) & LEFTB(MID(A2, FIND(” “, A2) + 1), 1)
3Jane Smith
4Mark Johnson
• In cell B2, use the formula `=LEFTB(A2, 1) & LEFTB(MID(A2, FIND(" ", A2) + 1), 1)` to extract the first letter of the first name and the first letter of the last name.
• The formula combines the full name’s leftmost character with the last name’s leftmost character after the space.
• The result in cell B2 will be “JD” for the name “John Doe”.

Example 6: Extracting Initials with Last Name First Sometimes, you may need to extract initials with the last name appearing first. Here’s how you can achieve that:

AB
1Full NameInitials
2John Doe=LEFTB(MID(A2, FIND(” “, A2) + 1), 1) & LEFTB(A2, 1)
3Jane Smith
4Mark Johnson
• In cell B2, use the formula `=LEFTB(MID(A2, FIND(" ", A2) + 1), 1) & LEFTB(A2, 1)` to extract the first letter of the last name followed by the first letter of the first name.
• The formula combines the last name’s leftmost character with the full name’s leftmost character before the space.
• The result in cell B2 will be “DJ” for the name “John Doe”.

Example 7: Extracting First Word from Text Suppose you have a list of product descriptions in column A and want to extract the first word from each description. Here’s how you can do it:

AB
1DescriptionFirst Word
2Smartphone with a high-resolution camera=LEFTB(A2, FIND(” “, A2) – 1)
3Laptop with Intel processor
• In cell B2, use the formula `=LEFTB(A2, FIND(" ", A2) - 1)` to extract the first word from the description.
• The formula uses the FIND function to locate the position of the first space, subtracts 1 to exclude the area, and then extracts the left portion of the text.
• The result in cell B2 will be “Smartphone” for the description “Smartphone with a high-resolution camera”.

Example 8: Extracting Leftmost Characters from Numbers The LEFTB function can also be used with numeric values. Suppose you have a list of account numbers in column A and want to extract the leftmost three characters. Here’s how you can do it:

AB
1Account NumberLeft 3 Characters
2123456789=LEFTB(TEXT(A2,”0″), 3)
3987654321
4567890123
• In cell B2, use the formula `=LEFTB(TEXT(A2,"0"), 3)` To convert the numeric value to text using the TEXT function, extract the leftmost three characters using the LEFTB function.
• The result in cell B2 will be “123” for the account number “123456789”.

Example 9: Extracting Leftmost Characters with IF Function Suppose you have a list of product codes in column A and want to extract the leftmost two characters only if the code starts with “PC”. Otherwise, return an empty string. Here’s how you can do it:

AB
1Product CodeExtracted Code
2PC12345=IF(LEFTB(A2, 2) = “PC”, LEFTB(A2, 2), “”)
3LAP789
4PC98765
• In cell B2, use the formula `=IF(LEFTB(A2, 2) = "PC", LEFTB(A2, 2), "")` Check if the product code’s leftmost two characters equal “PC”. If true, it extracts the leftmost two characters; otherwise, it returns an empty string.
• The result in cell B2 will be “PC” for the product code “PC12345”.

Example 10: Extracting Leftmost Characters and Combining with Other Text In this example, we will extract the leftmost three characters from a code and combine them with other text using the CONCATENATE function.

AB
1CodeFull Code
2ABC123=CONCATENATE(“CODE: “, LEFTB(A2, 3))
3XYZ789
4PQR456
• In cell B2, use the formula `=CONCATENATE("CODE: ", LEFTB(A2, 3))` to combine the text “CODE: ” with the leftmost three characters of the code.
• The result in cell B2 will be “CODE: ABC” for the code “ABC123”.

These examples demonstrate how to use the LEFTB function nested with different functions commonly used in business scenarios. Combining the LEFTB function with other functions allows you to manipulate and extract specific portions of text or numbers based on your business needs.

💡 Part 3: Tips and Tricks

• The LEFTB function is handy when dealing with languages that require double-byte characters.
• When using the LEFTB function, consider the byte length rather than the character length, as it may vary between languages.
• Be cautious when working with mixed single-byte and double-byte character sets, as the LEFTB function will count each character accordingly.
• Always double-check the result of the LEFTB function to ensure it extracts the intended portion of the text string based on the specified number of bytes.

These examples demonstrate how to use the LEFTB function to extract a specified number of bytes from the left side of a text string in Microsoft Excel. The function is handy when working with DBCS languages or when byte length is crucial for data manipulation or analysis.