Blog
No Responses May 23, 2024

How to Extract Text After a Hyphen in Google Sheets or Excel

In Google Sheets or Excel, there are times when you need to extract text from a cell that follows a specific delimiter, such as a hyphen. Recently, I faced a similar challenge with a list of item names accompanied by code numbers. Each item followed this pattern:

16(184) - Paris Pati (4)
16(184) - Paris Pati (4)
16(183) - Paris Pati (10)
16(168) - EXTERIOR WALL PRIMER (BP white Lite)
16(167) - INTERIOR WALL PRIMER (Walmasta)
16(166) - Wall putty (50kg)
16(192) - STAIR STEP TILES (300x1200)
16(191) - TOILET WALL TILES(300X450) (6 pcs) AGL (Comfy Decor)
16(190) - TOILET WALL TILES(300X450) (6 pcs) AGL (Comfy Beige)
16(189) - TOILET FLOOR TILES (300X300) (9 pcs) AGL (Comfy brown)
16(188) - FLOOR TILES (600x600), 4 pcs AGL (Deluxe Morocco)
16(201) - SAND
16(200) - CEMENT
16(50) - modular blank cover
16(49) - socket (16 Amp)
16(48) - switch (16 Amp)

I needed to extract only the item names, leaving behind the code numbers and any additional prefixing text. Here’s how I achieved this using a simple formula:

=TRIM(MID(A1, FIND("-", A1) + 2, LEN(A1)))

Let’s break down the formula and explain how it works:

Formula Breakdown

  1. FIND(“-“, A1) + 2: This part of the formula locates the position of the hyphen in the text within cell A1 and then adds 2 to move the starting position to the first character after the hyphen and the following space.
  2. MID(A1, FIND(“-“, A1) + 2, LEN(A1)): The MID function extracts the substring starting from the position calculated in step 1 and extends to the end of the text in cell A1.
  3. TRIM(…): The TRIM function removes any leading or trailing spaces from the extracted text, ensuring that the final result is clean and free of any unnecessary whitespace.

Applying the Formula

Here’s how I applied the formula to achieve my desired result. For each item in my list, I entered the formula into a new column, referencing the corresponding cell containing the original item text. Here’s an example of how the extracted text looked after applying the formula:

INTERIOR WALL PRIMER (Walmasta)
Wall putty (50kg)
STAIR STEP TILES (300x1200)
TOILET WALL TILES(300X450) (6 pcs) AGL (Comfy Decor)
TOILET WALL TILES(300X450) (6 pcs) AGL (Comfy Beige)
TOILET FLOOR TILES (300X300) (9 pcs) AGL (Comfy brown)
FLOOR TILES (600x600), 4 pcs AGL (Deluxe Morocco)
SAND
CEMENT
modular blank cover
socket (16 Amp)
switch (16 Amp)

Conclusion

Using the formula =TRIM(MID(A1, FIND("-", A1) + 2, LEN(A1))) in Google Sheets or Excel, I was able to efficiently extract the relevant text after the hyphen, removing any unnecessary prefixes. This technique is not only useful for my specific case but can be applied to various scenarios where you need to isolate and extract portions of text following a delimiter