64 nesting limit

[rvvasilev96]

hello,

i would like to ask a question, regarding a problem i've faced recently.

I want to take out a specific information from a text, which varies, for example:
asdjhbasdas1234BGasdhasd - i want to take out and display just the 1234BG
iaiopsunlkas1234BGpiouqws- i want to take out and display just the 1234BG

So, i used the following formula:
=if(iserror(search("1234BG";A01))=FALSE;"1234BG"), and nested it 64 times, but i have many more variations i need to cover,

Can you give me some ideas?

Thank you!

 

[sandy666]

try Power Query

text	result
asdjhbasdas1234BGasdhasd	1234BG
iaiopsunlkas1234BGpiouqws	1234BG

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.AddColumn(Source, "result", each Text.Select([text],{"0".."9","A".."Z"}))
in
    Result

I assumed this is representative example

 

[arthurbr]

Use "*1234BG*" instead of "1234BG" in your forumla

 

[Macropod]

If the substring you're after is the first (or only) substring that begins with a number in the larger string, you could use:
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),6)
to retrieve all 6 characters from any string.

 

[Peter_SSs]

Welcome to the MrExcel board!

Give us some more examples of texts and what you want extracted and try to put a written 'rule' about what should or should not be extracted.
What about 10-20 samples and expected results using XL2BB

 

[FormR]

Can you give me some ideas?

Hi, welcome to the forum.

If there are no hard and fast rules, then here is an option where you can create a list of the strings you are searching for in a range of cells.

Book2
	A	B	C	D
1	Text to search	Result		List of earch terms
2	asdjhbasdas1234BGasdhasd	1234B		1234B
3	asdjhbasDas34BGasdhasd	Das34B		Das34B
4	asdjhbasdadsaDSAMBGasdhasd
5	asdjhbasDas34BGasdhasd	Das34B
6	asdjhbasdas1234BGasdhasd	1234B
7	asdjhbasdadsaDSAMBGasdhasd
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=IFNA(LOOKUP(2,1/ISNUMBER(SEARCH($D$2:$D$3,A2)),$D$2:$D$3),"")