TEXTBEFORE/MID/FIND Formula Help

[rachel06]

Hi!

I use this formula to find a string of "0196". Wondering if there is a way to add more strings for it to find? Like if it finds 0196 or 0249 to perform the formula?

=TEXTBEFORE(MID(A2,FIND("0196",A2),255),{" ","."})

I've tried adding it in the {} like the delimiters are, but then it's using two columns instead of just one. - IE I have the formula in cell C3, and it's searching for 0196 there and 0249 in D3. I hope this makes sense!

In case you need some dummy data....
Company ABC 0196-9999 Rate Sheet 01-01-2025
Company XYZ 0249-9999 Rate Sheet 01-01-2025

I'd like the formula to pull out 0196-9999 or 0249-9999 with these, in the cell the formula is in. The formula above works for the 0196, but I cannot figure out how to incorporate the 0249. There may be more I need to add later I'm sure when I get a working formula for this, it'll be easy to add more as needed.

Any help is greatly appreciated!

 

[Cubist]

Try:

Tips2025.xlsx
	A	B
1
2	Company ABC 0196-9999 Rate Sheet 01-01-2025	0196-9999
3	Company XYZ 0249-9999 Rate Sheet 01-01-2025	0249-9999
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=LET(t,TEXTSPLIT(A2," "),FILTER(t,MAP(t,LAMBDA(m,OR(ISNUMBER(FIND({"0196","0249"},m)))))))

 

[Rick Rothstein]

I think this single formula will do what you want (change the two occurrences of the range to match your actual data)...

=IFERROR(MID(A2:A9,BYROW(IFERROR(FIND({"0196","0249"},A2:A9),""),CONCAT),9),"")

 

[rachel06]

I wish I could mark you both as solutions. Thanks so much! I didn't think it'd be that complex of a formula!

 

[Dave Patton]

TextBefore.xlsm
	A		C	D
1
2	Company ABC 0196-9999 Rate Sheet 01-01-2025		0196-9999	0196-9999
3	Company XYZ 0249-9999 Rate Sheet 01-01-2025		0249-9999	0249-9999
4	Company XYZ 2249-9999 Rate Sheet 01-01-2025
4a

Cell Formulas
Range	Formula
C2:C4	C2	=IF(COUNT(FIND({"0196","0249"},A2)),TEXTAFTER(TEXTBEFORE(A2," ",3)," ",2),"")
D2:D4	D2	=IF(COUNT(FIND({"0196","0249"},A2)),LEFT(TEXTAFTER(A2," ",2),9),"")

 

[Cubist]

Here's another solution that is a bit more efficient and robust (not relying on the position of the delimiters or fixed length of the code string).

Tips2025.xlsx
	A	B	C
1		Single cell	Array
2	Company ABC Test 0196-9999 Rate Sheet 01-01-2025	0196-9999	0196-9999
3	Company 0249-9999 Rate Sheet 01-01-2025	0249-9999	0249-9999
4	Company XYZ 2249-9999 Rate Sheet 01-01-2025
5	Company ABC XYZ 0196-9999922222 Rate Sheet 01-01-2025	0196-9999922222	0196-9999922222
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=MAP(A2:A5,LAMBDA(m,LET(t,TEXTSPLIT(m," "),CONCAT(XLOOKUP("*"&{"0196","0249"}&"*",t,t,"",2)))))
B2:B5	B2	=LET(t,TEXTSPLIT(A2," "),CONCAT(XLOOKUP("*"&{"0196","0249"}&"*",t,t,"",2)))
Dynamic array formulas.