Formula to find text

A

Access Beginner

Active Member
Joined
Nov 8, 2010
Messages
311
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I have a range where the following text can appear "Zone: Zone1".Now this text can appear in different cells, so one week it will be in cell E29 and the next week it will be in cell E40 (data is sent to me this way). Is there a formula, that can search a range, say E29 to E40, look for the text "Zone:" and return me whatever comes after? I will also use this for other searches as well, as an example, I'll use it to seach for Request: and return whatever comes after that.

The difference between these two is that "Zone:" will only ever have at most 30 characters after and "Reason:" is a may have up to 200 characters after it.

Thanks in advance if you can help

Haydn
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
3 questions

1. so if you are searching for "Zone:" and cell e29 is "Zone: Zone1" , "what comes after" is " Zone1" . is that what you are after ?
2 Is the range you are searching in just column "E" or could the range be in say RANGE e20:f40 ?
3 Will there only be one cell containing the text or could there be mutiple cells ?
 
Upvote 0
hitrreeZone: Zone14567786544335
hitrree=INDIRECT("E"&MATCH("Zone*",E:E,0))
hitrree
hitrreeZone14567786544335
hitrree=RIGHT(INDIRECT("E"&MATCH("Zone*",E:E,0)),LEN(INDIRECT("E"&MATCH("Zone*",E:E,0)))-FIND(":",INDIRECT("E"&MATCH("Zone*",E:E,0)))-1)
hitrree
hitrree
hitrree
hitrreemaybe this helps
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
Zone: Zone14567786544335
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
<colgroup><col width="64" style="width: 48pt;" span="3"> <col width="977" style="width: 733pt; mso-width-source: userset; mso-width-alt: 35730;"> <tbody> </tbody>
 
Upvote 0
Thanks for you reply.
Question 1: yes Zone1 (not the real Zone name though) is what I would like returned after
Question2: The range will only be in Column E
Question3: There will be only one cell contain the text
liveinhope said:
3 questions

1. so if you are searching for "Zone:" and cell e29 is "Zone: Zone1" , "what comes after" is " Zone1" . is that what you are after ?
2 Is the range you are searching in just column "E" or could the range be in say RANGE e20:f40 ?
3 Will there only be one cell containing the text or could there be mutiple cells ?
Click to expand...
 
Upvote 0
Hi Again,

Is there a way to refine the range from E:E to say E29 to E50. I tried changing E:E to E29:E50 where each occurence of E:E existed, but it reutned "#N/A"
Cheers
vogel997 said:
hitrree
Zone: Zone14567786544335
hitrree
=INDIRECT("E"&MATCH("Zone*",E:E,0))
hitrree
hitrree
Zone14567786544335
hitrree
=RIGHT(INDIRECT("E"&MATCH("Zone*",E:E,0)),LEN(INDIRECT("E"&MATCH("Zone*",E:E,0)))-FIND(":",INDIRECT("E"&MATCH("Zone*",E:E,0)))-1)
hitrree
hitrree
hitrree
hitrree
maybe this helps
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
Zone: Zone14567786544335
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree

<TBODY>
</TBODY>
Click to expand...
 
Upvote 0
hitrreeZone: Zone14567786544335
hitrree=INDIRECT("E"&ROW(E28)+MATCH("Zone*",E29:E50,0))
hitrree
hitrree
hitrreeZone14567786544335
hitrree=RIGHT(INDIRECT("E"&ROW(E28)+MATCH("Zone*",E29:E50,0)),LEN(INDIRECT("E"&ROW(E28)+MATCH("Zone*",E29:E50,0)))-FIND(":",INDIRECT("E"&ROW(E28)+MATCH("Zone*",E29:E50,0)))-1)
hitrree
hitrree
hitrreemaybe this helps
hitrree
hitrree
hitrree
hitrree
hitrree
hitrree
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="1215" style="width: 911pt; mso-width-source: userset; mso-width-alt: 44434;"> <tbody> </tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
No formulas working to rename sheets within workbook
Replies
9
Views
330
Joe4
Joe4
L
  • Question
Find text within a cell using formula
Replies
9
Views
433
Fluff
Fluff
L
  • Solved
Macro to run differently depending on text in drop down cell.
2
Replies
10
Views
724
6StringJazzer
6StringJazzer
S
  • Solved
Using the Find function within VBE
Replies
5
Views
182
RoryA
RoryA
M
  • Solved
Formula to find words in list of text data
Replies
9
Views
407
AhoyNC
A

Forum statistics

Threads
1,216,727
Messages
6,132,354
Members
449,720
Latest member
NJOO7

Share this page

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