=SUBSTITUTE(A1,LEFT(A1,FIND(">ref:",A1)-1),"##")
Ever found yourself struggling with messy data in Excel or Google Sheets? Say hello to a smart formula that makes text neat and structured in just one step! 🚀
📌 The Problem:
Imagine you have a list of product details like this:
ProductXYZ >ref:12345But you only need the reference part (>ref:12345) while removing the extra text before it.
💡 The Solution:
Here’s a powerful yet simple formula to do that:
=RIGHT(A1, LEN(A1) - FIND(">ref:", A1) + 1)🔍 How It Works:
- FIND(">ref:", A1) → Locates the position of
>ref:in the text. - LEN(A1) - FIND(">ref:", A1) + 1 → Calculates how much of the text we need to keep.
- RIGHT(A1, ... ) → Extracts only the part after
>ref:while removing the unnecessary part.
🎯 The Output:
| Before | After |
|---|---|
| ProductXYZ >ref:12345 | >ref:12345 |
| ItemABC >ref:67890 | >ref:67890 |
🎉 Clean, professional, and hassle-free! This is a must-know trick for data management and automation lovers!
💬 Try it out and let me know your thoughts in the comments! 🚀
Tags:
Excel Tricks