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!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,174
Office Version
  1. 2010
Use "*1234BG*" instead of "1234BG" in your forumla
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,515
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,467
Office Version
  1. 365
Platform
  1. Windows
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),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,504
Messages
5,602,060
Members
414,498
Latest member
jordanmiller7890

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
Top