rvvasilev96

New Member
Joined
Mar 23, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
try Power Query
textresult
asdjhbasdas1234BGasdhasd1234BG
iaiopsunlkas1234BGpiouqws1234BG

Rich (BB code):
// 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
 
Upvote 0
Use "*1234BG*" instead of "1234BG" in your forumla
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
ABCD
1Text to searchResultList of earch terms
2asdjhbasdas1234BGasdhasd 1234B1234B
3asdjhbasDas34BGasdhasd Das34BDas34B
4asdjhbasdadsaDSAMBGasdhasd  
5asdjhbasDas34BGasdhasd Das34B
6asdjhbasdas1234BGasdhasd 1234B
7asdjhbasdadsaDSAMBGasdhasd  
Sheet1
Cell Formulas
RangeFormula
B2:B7B2=IFNA(LOOKUP(2,1/ISNUMBER(SEARCH($D$2:$D$3,A2)),$D$2:$D$3),"")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top