search within a cell and return result

tintin1012000

Board Regular
Joined
Apr 27, 2011
Messages
237
im using below formula
HTML:
=IFERROR(IFERROR(MID(B2;FIND("*available for*";B2)+14;10);MID(B2;FIND("*Error: Material*";B2)+16;10));"")

when I find the text ''available for'' i want to return the next word, ie what ever is after the next space and before the space after that, i need this as the result can vary in length and my formula is limiting me

eg

cell B2 contains, available for sunday and Monday then result would be ''sunday''
or it could contain available for 987 today then result would be ''987''
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Re: search within a cell and retun result

tintin1012000,

How about something like this?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">available for sunday and Monday</td><td style=";">sunday</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">available for 987 today</td><td style="text-align: right;;">987</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">B2," ",REPT(<font color="Purple">" ",100</font>)</font>),200,100</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

The formula in cell C2, copied down:

=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",100)),200,100))
 

tintin1012000

Board Regular
Joined
Apr 27, 2011
Messages
237
Re: search within a cell and retun result

tintin1012000,

How about something like this?

Excel 2007
BC
2available for sunday and Mondaysunday
3available for 987 today987
4

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C2=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",100)),200,100))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



The formula in cell C2, copied down:

=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",100)),200,100))

this formula does not work

another example of cell B2
result I want back is S116490097

HTML:
Message 1005 - ASSEMBLE XML failed for SFC: YL006QAB59060 - Error: Not enough Floor Stock is available for S116490097 to assemble requested quantity completely (Message 13147); Component: "66151003103301" Revision: "00" Loc: "4" Track: "49" Div: "1"Station: "D3_2"C:\FTPMES\SMT\failure\P10147750SNYL006QAB59060_B1-20170112221615.xml. Station "D3_2"
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Re: search within a cell and retun result

this formula does not work

another example of cell B2
result I want back is S116490097

tintin1012000,

Can we see an actual screenshot of the raw data worksheet, with the strings in in column B, beginning in cell B2, and, in column C, beginning in cell C2, what the results should be?


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com


If you are not able to provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 

tintin1012000

Board Regular
Joined
Apr 27, 2011
Messages
237

ADVERTISEMENT

Re: search within a cell and retun result

tintin1012000,

Can we see an actual screenshot of the raw data worksheet, with the strings in in column B, beginning in cell B2, and, in column C, beginning in cell C2, what the results should be?


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com


If you are not able to provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

Hi Hiker

I have limited internet access, is below any good

I need the next full word or text after ''available for' this can be any length

HTML:
Message 1005 - ASSEMBLE XML failed for SFC: YK006QAC26210 - Error: Not enough Floor Stock is available for S29917451 to assemble requested quantity completely (Message 13147); Component: "10038742" Revision: "00" Loc: "3" Track: "21" Div: "1"Station: "X4S_3"C:\FTPMES\SMT\failure\B_P10119671SNYK006QAC26210_T2-20170112100000617.xml. Station "X4S_3"Message 1005 - ASSEMBLE XML failed for SFC: YK006QAC26230 - Error: Not enough Floor Stock is available for SBGZSSM to assemble requested quantity completely (Message 13147); Component: "66250470940301" Revision: "01" Loc: "1" Track: "32" Div: "2"Station: "X4S_2"C:\FTPMES\SMT\failure\B_P10119671SNYK006QAC26230_T2-20170112104233015.xml. Station "X4S_2"Message 1005 - ASSEMBLE XML failed for SFC: YL006QAB59980 - Error: Not enough Floor Stock is available for S116cde490097 to assemble requested quantity completely (Message 13147); Component: "66151003103301" Revision: "00" Loc: "4" Track: "49" Div: "1"Station: "D3_2"C:\FTPMES\SMT\failure\P10147750SNYL006QAB59980_B1-20170112224756.xml. Station "D3_2"
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
870
Re: search within a cell and retun result

Whenever I have something like this, I start small and work up.

I noticed that a Regex expression might be a neat way of solving this, but could not get it to work, so went back to basics.

Text to search is in A1, text to search for is in Z1. Adjust to suit

=MID(A1,FIND(Z1,A1)+LEN(Z1)+1,FIND(" ",A1,FIND(Z1,A1)+LEN(Z1)+1)- (FIND(Z1,A1)+LEN(Z1)+1))

using your last string that produces S116490097 for a length of 10 ?

The +1 is required if you have no trailing space is in the string to find. Play with it until you are happy in the way it works.

If you were doing this a lot, I'd write a UDF and use that.

HTH
 
Last edited:

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Re: search within a cell and retun result

I have limited internet access, is below any good

HTML Code:

tintin1012000,

I am not going to try and separate what is in your HTML Code: display.

If you can not supply your actual raw data workbook/worksheet, or, a screenshot, per my last reply #4, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
870
Re: search within a cell and retun result

HIker95,

I took the code to be the contents of the cell?

O/P just used wrong tags?

Code:
Message 1005 - ASSEMBLE XML failed for SFC: YK006QAC26210 - Error: Not enough Floor Stock is available for S29917451 to assemble requested quantity completely (Message 13147); Component: "10038742" Revision: "00" Loc: "3" Track: "21" Div: "1"Station: "X4S_3"C:\FTPMES\SMT\failure\B_P10119671SNYK006QAC26210_T2-20170112100000617.xml. Station "X4S_3"Message 1005 - ASSEMBLE XML failed for SFC: YK006QAC26230 - Error: Not enough Floor Stock is available for SBGZSSM to assemble requested quantity completely (Message 13147); Component: "66250470940301" Revision: "01" Loc: "1" Track: "32" Div: "2"Station: "X4S_2"C:\FTPMES\SMT\failure\B_P10119671SNYK006QAC26230_T2-20170112104233015.xml. Station "X4S_2"Message 1005 - ASSEMBLE XML failed for SFC: YL006QAB59980 - Error: Not enough Floor Stock is available for S116cde490097 to assemble requested quantity completely (Message 13147); Component: "66151003103301" Revision: "00" Loc: "4" Track: "49" Div: "1"Station: "D3_2"C:\FTPMES\SMT\failure\P10147750SNYL006QAB59980_B1-20170112224756.xml. Station "D3_2"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

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