LEFTB Function in Excel

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:

• 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:

• 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:

• 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:

• 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:

• 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:

• 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.

• 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.