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''
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
Sheet1
Cell Formulas
RangeFormula
C2=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",100)),200,100))


The formula in cell C2, copied down:

=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",100)),200,100))
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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