Forum Discussion

jamescosten's avatar
jamescosten
Copper Contributor
Feb 22, 2024
Solved

Right Function but with Numbers Upto.

I want to trim the numbers from the left column but I want it to be able to go upto 4 digits.   Its not returning correctly, How can i do this?
excel
Formulas and Functions
smylbugti222gmailcom's avatar
smylbugti222gmailcom
Iron Contributor
Feb 22, 2024

jamescosten 

Based on the information you provided, the formula in cell B1 isn't working correctly because it's using the RIGHT function, which extracts a specified number of characters from the right side of the text string. In this case, you want to extract characters from the left side.

Here are two alternative approaches to achieve the desired outcome:

Method 1: Using the LEFT function

  1. In cell B1, enter the following formula:
Excel
=LEFT(A1, LEN(A1)-FIND("*",SUBSTITUTE(A1, "WTRF", "", LEN(A1)-LEN(SUBSTITUTE(A1, "WTRF", "")))))
 
  1. Press Enter to copy the formula down to the remaining cells in column B.

Explanation:

  • LEFT(A1, LEN(A1)-...): This part extracts characters from the left side of the text in cell A1, up to a specific number of characters.
  • LEN(A1): This calculates the total length of the text in cell A1.
  • FIND("*",SUBSTITUTE(A1, "WTRF", "", LEN(A1)-LEN(SUBSTITUTE(A1, "WTRF", "")))): This part finds the position of the first occurrence of the text "WTRF" within cell A1, replacing it with an empty string, and then subtracts the length of the replaced text from the original length. This effectively removes "WTRF" from the string and returns the position where the remaining characters begin.
  • By subtracting this position from the total length, we determine the number of characters to extract from the left side using the LEFT function.

Method 2: Using the MID function

  1. In cell B1, enter the following formula:
Excel
=MID(A1, 5, LEN(A1)-4)
 
  1. Press Enter to copy the formula down to the remaining cells in column B.

Explanation:

  • MID(A1, 5, LEN(A1)-4): This extracts characters from the middle of the text in cell A1, starting at the 5th position (assuming "WTRF" occupies the first 4 characters) and extracting all characters until the end (represented by LEN(A1)-4).

Both methods should effectively extract the desired characters from the left side of the text in column A, considering up to 4 digits. Choose the method that best suits your preference and data structure.

Resources