Solved: Extract text and numbers - Page 2

Jeremy_F · 08-22-2019 09:56 AM

1 - How to extract all text values and ignore number? say Product 154Z, product name 325 SC, product new name S41V etc

2 - Find the position of the first number in a text ?

Fabian_Weller

@Jeremy_F You can try thin one:

LEFT("product name 753VS",
ANY(SORT(LIST(
FIND("0","product name 753VS"),
FIND("1","product name 753VS"),
FIND("2","product name 753VS"),
FIND("3","product name 753VS"),
FIND("4","product name 753VS"),
FIND("5","product name 753VS"),
FIND("6","product name 753VS"),
FIND("7","product name 753VS"),
FIND("8","product name 753VS"),
FIND("9","product name 753VS"))
- LIST("0")))-1)

LIST(FIND()): Find the position of numbers 0 to 9, and create a LIST of those positions.
-LIST(“0”): Delete all Zeros from that LIST.
SORT: Order this LIST from 1to9.
ANY: Take the first number.
LEFT(“product name 753VS”, “Position” - 1): Decrease the Position -1

View solution in original post