The tutorial explains the syntax of the Excel FIND and SEARCH functions and provides formula examples of advanced non-trivial uses. Continue reading
Comments on: Excel FIND and SEARCH functions with formula examples
by Svetlana Cheusheva, updated on
Comments page 6. Total comments: 446
I have a column A with multiple cells having a common value.
I also have a column B with equal number of cells each having a unique value.
I want to do a FInd and Replace such that it finds the common value in each cell of Column A and replace it with the unique value of adjoining cell of column B,
See screenshot for more clarity - https://prnt.sc/t0ksyn
Hello!
I hope you have studied the recommendations in the above tutorial.The Find function can search in only one cell. I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What does "common value in each cell of Column A" mean? In column A, all cells have the same values. If a value is written in a cell, then it can be changed either manually or using the VBA macro. Therefore, the values in column A, you cannot change the values using the formula. Explain in more detail what you want to do.Thank you!
Hello Sir, I need a Formula/Function to show variance with multiple criteria…
Function needs applied to E:E in examples below- if it is needed to add extra columns, we can. If it needs to be done in VBA form, we can do that as well.
A - FIND B:B WITHIN F:F
SEARCH ( B:B , F:F )
B - TAKE THE MINIMUM OF D:D, WHERE “A” CRITERIA MATCHES (MULTIPLE SKU WITH DIFFERENT PRICING)
MIN ( D3 : D11 )
C - AND THEN MULTIPLY THAT MINIMUM BY 1.25
MIN ( D3 : D11 ) * 1.25
D – TAKE THAT NEW NUMBER AND APPLY IT TO ALL ROWS IN COLUMN D WHERE “A” & “B” CRITERIA ARE TRUE
(not sure how to do this part)
RAW DATA
1 B C D E F
2 SKU MKT MARKET PRICE 25% VARIANCE SKUMKT
3 1234 CHINA 18.98 1234CHINA
4 1234 AUSTRALIA 18.98 1234AUSTRALIA
5 1234 USA 17.03 1234USA
6 9876 CHINA 17.11 9876CHINA
7 9876 AUSTRALIA 18.98 9876AUSTRALIA
8 9876 USA 18.28 9876USA
9 4567 CHINA 18.98 4567CHINA
10 4567 AUSTRALIA 18.98 4567AUSTRALIA
11 4567 USA 18.63 4567USA
WHAT IT SHOULD LOOK LIKE WHEN COMPLETE
1 B C D E F
2 SKU MKT MARKET PRICE 25% VARIANCE SKUMKT
3 1234 CHINA 18.98 21.29 1234CHINA
4 1234 AUSTRALIA 18.98 21.29 1234AUSTRALIA
5 1234 USA 17.03 21.29 1234USA
6 9876 CHINA 17.11 21.38 9876CHINA
7 9876 AUSTRALIA 18.98 21.38 9876AUSTRALIA
8 9876 USA 18.28 21.38 9876USA
9 4567 CHINA 18.98 23.29 4567CHINA
10 4567 AUSTRALIA 18.98 23.29 4567AUSTRALIA
11 4567 USA 18.63 23.29 4567USA
Am I not able to do a formula like what it is I am hoping for?
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
We need a Formula/Function to show variance with multiple criteria…
Function needs applied to E:E in examples below.
We need to find the same SKU numbers (column B) that have multiple markets (column C), find the lowest price of that SKU between those markets(column D), and then multiply that lowest price by 25% and be entered in column E for all of the SKUs that match.
A - FIND B:B WITHIN F:F
SEARCH ( B:B , F:F )
B - TAKE THE MINIMUM OF D:D, WHERE “A” CRITERIA MATCHES (MULTIPLE SKU WITH DIFFERENT PRICING)
MIN ( D3 : D11 )
C - AND THEN MULTIPLY THAT MINIMUM BY 1.25
MIN ( D3 : D11 ) * 1.25
D – TAKE THAT NEW NUMBER AND APPLY IT TO ALL ROWS IN COLUMN D WHERE “A” & “B” CRITERIA ARE TRUE
(not sure how to do this part)
RAW DATA
1.B.....C..........D..............E.............F
2 SKU .MARKET.....MARKET PRICE. 25% VARIANCE. SKUMKT
3 1234 CHINA ......18.98 ........()...........1234CHINA
4 1234 AUSTRALIA ..18.98 .........()...........1234AUSTRALIA
5 1234 USA ........17.03 .......().............1234USA
6 9876 CHINA ......17.11..........().......... 9876CHINA
7 9876 AUSTRALIA ..18.98 .........()...........9876AUSTRALIA
8 9876 USA ........18.28..........().......... 9876USA
9 4567 CHINA ......18.98 .........()...........4567CHINA
10 4567 AUSTRALIA ..18.98.........().......... 4567AUSTRALIA
11 4567 USA ........18.63 ........()............4567USA
WHAT IT SHOULD LOOK LIKE WHEN COMPLETE
1.B.....C..........D..............E...............F
2 SKU .MARKET..... MARKET PRICE.. 25% VARIANCE ..SKUMKT
3 1234 CHINA ......18.98 .........21.29 .........1234CHINA
4 1234 AUSTRALIA ..18.98 .........21.29 .........1234AUSTRALIA
5 1234 USA ........17.03 .........21.29 .........1234USA
6 9876 CHINA ......17.11 .........21.38 .........9876CHINA
7 9876 AUSTRALIA ..18.98 .........21.38 .........9876AUSTRALIA
8 9876 USA ........18.28 .........21.38 .........9876USA
9 4567 CHINA ......18.98 .........23.29 .........4567CHINA
10 4567 AUSTRALIA .18.98 .........23.29 .........4567AUSTRALIA
11 4567 USA .......18.63 .........23.29 .........4567USA
Hello!
Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
HI FOrum,
If i need to extract text from intial and last name with no character break, how do i do it?
Hello!
I recommend to study this article. Then describe the problem in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.
Hi Sir,
In one column i have a string from which i have to extract the exact unique code which starts with "ABCD" and followed by 4 numeral characters (Example-ABCD2345)
Example String-
Information: ABCD: Update for Application 1 (ABCD2140) Rel. 1.01
Expected output- ABCD2140
Solution- =IFERROR(MID(C3,FIND("(ABCD",C3),FIND(")",C3)-FIND("(ABCD",C3)),"NA")
The solution works for above String, but it fails in cases where any "(" comes before "(ABCD".
Example-(XYZ) external – Application 2 (ABCD2003) Rel. 1.02
Expected Output- ABCD2003
Could you help me get a common solution which works for both the cases.
Hello!
If I understand your task correctly, the following formula should work for you:
="ABCD"&MID(C3,FIND("(ABCD",C3,1)+5,4)
I hope this will help, otherwise please do not hesitate to contact me anytime.
I have a string of text, similar to the following: This well requires cleaning; Y; $2,000; WB Abandonment; Y; $12,000; Other; N; $5,000.
I'd like to use a formula to search the string and if "WB Abandonment; Y;" is in the string it would return "$12,000". If "WB Abandonment; Y;" is not in the string, it returns blank. I'm struggling with the mid search formula since the various lines of text have a different number of components the well requires. Any help you can provide would be greatly appreciated.
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(IFERROR(SEARCH("WB Abandonment; Y;",A1,1),0) > 0,"$ 12,000","")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Sorry, I wasn't clear. The value isn't always $ 12,000. I'm looking to return the value that follows "WB Abandonment; Y;".
Hello Sue!
To get all the text after phrase “WB Abandonment; Y;”, use the formula
=IF(IFERROR(SEARCH("WB Abandonment; Y;",A1,1),0)>0, MID(A1,SEARCH("WB Abandonment; Y;",A1,1)+19,100),"")
If you wrote exactly what you want to receive, we would solve your problem faster.
Unfortunately this formula returns all the text after "WB Abandonment; Y;". I'm hoping to be able to only pull the $ amount after "WB Abandonment; Y;" and before the next ;.
Hi sir,
I have a question, my question is that the way we have used the left function with find ,same way we can't use the right function with find to get the last name.
Hi Alexander,
Thank you for your post. I have been unable to find any information to help me with my issue - perhaps you can help?
I have a text string in column L. Within this text string I can find a series of alternatives (which are contained in the OR brackets below). I've tried to simplify a heavy, clunky formula (1) to a more elegant (2), but (2) isn't working - it's not returning any values even if I know the value is contained in column L.
(1) Monster: =IFERROR(IFERROR(IFERROR(IFERROR((IFERROR(MID(L2,FIND("DPTO",L2),50),MID(L2,FIND("CASA ",L2),50))),(MID(L2,FIND("LOC ",L2),50))),(MID(L2,FIND("OF",L2),50))),(MID(L2,FIND("PARC",L2),50))),(MID(L2,FIND("DEPT",L2),50)))
(2) Failed attempt to simplify:
=MID(L2,FIND(OR("DPTO","CASA ","DEPT","LOC ","OF","PARC"),L2),50)
Am I doomed to clunkiness? Or am I making a silly mistake?
Thanks in advance!
Hello Alex!
For me to be able to help you better, please describe your task in more detail. Do you want to derive these values using a formula, or simply specify TRUE or FALSE, or derive some part of the cell from column L? What result should your formula return? Please let me know in more detail what you were trying to find, what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.
The result of the formula should be the string text starting from the position found by the FIND formula (when it finds any of the alternatives included in the OR function brackets in equation 2) and the following 50 characters (which is why the MID formula is there).
Please let me know if you need more information!
Hello Alex!
If I understand your task correctly, the following formula should work for you:
=MID(L2,SMALL(IFERROR(FIND({"DEPT","CASA","OF","PARC"},L2,1),""),1),50)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Unfortunately it doesn't work. I had previously tried putting an array in FIND but that didn't work either. Even just =FIND({"DEPT","CASA","OF","PARC"},L2,1) returns a #VALUE error instead of a place number.
The source string has a mix of text, numbers and blank spaces, General format, if that helps at all.
Hello Alex!
My formula works according to your explanation. Give an example of the data in cell L2 and the result you want to get. Explain what exactly the formula does not work in. Perhaps I misunderstood you. Or you didn’t say anything.
Your FIND formula will not work, because it does not handle the event when the value is not found.
OK, thank you for your time and patience!
Alexander,
Sorry to bother you again but I've discovered what it's doing (although not why - maybe you have an idea?). It's only taking the first term in the array and ignoring the rest. So with the formula as it is, it's picking out the DEPT but not CASA. If I change round the order of those terms within the array, putting CASA first and leaving DEPT second, it returns all values with CASA but not DEPT (or any of the others).
Can you imagine what can be going on? Do I need to configure something else?
Thanks!
Hello Alex!
My formula works correctly. Therefore, I can’t tell you the reason for the error. Try to run it on another computer. Some Excel settings might be interfering.
Alexander,
Thank you so much for your time and help. I did triple check and although the formula is correct, but it's still not working for me :/ I wonder if there's some configuration other than cell format I haven't thought about... Anyway, you've definitely helped me as much as you possibly could, and I'm very grateful for your time and patience.
Have a lovely weekend!
Alex
OK so here's an example.
Cell L has this: AVENIDA DEL VALLE S/N ALGARROBOS 4B CASA 21B
Your formula: =MID(L2,SMALL(IFERROR(FIND({"DEPT","CASA","OF","PARC"},L2,1),""),1),50)
Is giving me this: #VALUE
When I need it to give this: CASA 21B
i.e. return the string from the position found by the FIND formula.
Hello Alex!
I copied the formula from your post:
=MID(L2,SMALL(IFERROR(FIND({"DEPT","CASA","OF","PARC"},L2,1),""),1),50)
I inserted text into cell L2:
AVENIDA DEL VALLE S/N ALGARROBOS 4B CASA 21B
And you won’t believe it - everything works for me!
I got the text "CASA 21B".
I can only advise once again to check whether you wrote down the formula correctly.
I have been trying to fix this for days and it has me baffled.
The customer enters their postcode and I am trying to match it with a postcode area. Sometimes the customers put a space in their postcode and sometimes they don't, so I have a list of postcodes with a space and a list without a space and I need to search both lists but cannot get it to work when the postcode is entered without a space.
I have created a postcode sheet with Column A being postcodes with a space and Column B without a space and column C is the area match
For example, customer's postcode is "DL1 2NE" in cell Q2 on "Enquiries" sheet and I want to look to see if there is a match in the "Postcodes" sheet and if not put a value of "No"
Postcodes
Column A Column B Column C
TS1 2TR TS12TR Tees
DL1 2NE DL12NE Tees
=IF(ISNA(VLOOKUP(Q2,'postcodes'!A:C,3,FALSE)),"No",VLOOKUP(Enquiries!Q2,'postcodes'!A:C,3,FALSE))
This works when there is a space but returns the wrong value when there is no space (i.e. it should still find a match and show "Tees" but instead it results in "No"
Thank you for your consideration.
Nick
Hello Nick!
Remove spaces from the index using the SUBSTITUTE function. Instead of Q2, use SUBSTITUTE (Q2, " ", ""). Search for this value in column B of the “Postcodes” sheet, where there are also no spaces.
I hope it’ll be helpful.
Good morning,
I have an a column that I put monitored voltages in and these values will change each time I take the measurements. At the bottom of the column I have the =min and =max formula so I know those values. My question:
is it possible to use the Find or Search formula to locate the =max in the column?
cell #
30 3.9000
29 3.9000
28 3.9002
27 3.9008
26 3.8841
25 3.9000
24 3.9000
23 3.9002
22 3.9009
21 3.9754
67 3.9010
68 3.9000
69 3.9002
70 3.9008
71 3.8847
72 3.9000
73 3.9000
74 3.9002
75 3.9009
76 3.9748
=max 3.9754
=min 3.8841
I would like to find the cell# that is =max and have it automatically highlighted.
Thanks for any assistance that you can offer.
Best Regards,
Robert
Hello Robert!
To automatically highlight the maximum or minimum value with color, you do not need to use the formula. Use standard conditional formatting tools. Conditional Formatting - Top / Bottom Rules. Read more here.
Hello Sir,
I have read all of the comments but unfortunately could not find solution for my problem yet.
I have an excel with many rows which containing a large text. (from 20 to 500 words)
I need way to find multiple key words within that text and if it's possible to display or to direct me to that text. For example let's say I have a text " In order to purchase the item, you need more money" and i want to search "order" and "money".
So basically I need a search function with more than one string.
Thanks a lot.
Hello Cosmin!
I think that using formulas in this case is not necessary. Use the "Find and Select" tool on the Home menu tab.
I'd recommend you to have a look at our Ablebits Tools - Find & Replace.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Hi there,
I have read most of above but unfortunately could not find solution for my problem yet.
In Sheet1 i have a list of approx 300 employee surnames, assuming no duplicates.
in Sheet2 I have a list of 10000 lines with travel expense postings, most of them mention the family name of the employee at some part of the text. I need to create review cost by employee.
So far I was able to return true of false if the row includes an employee name or not with this formula =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet1!$A$1:$A$42;A2)))>0
but I need the formula to return the name for me instead of just true of false? So need to know not only at what charactar it starts but also the LEN so i can do it with MID ?
Thanks a lot
Thanks a lot
hello David!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Give some examples of the values from which you want to extract text. Also write what exactly you want to receive.I will try to help you.
Thank you!
Hi Alexander and thank you.
Sample: Sheet 2:
A1: Airplane ticket John Smith NYC-LAX
A2: $650.-
A3: =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet1!$A$1:$A$42;A2)))>0
Sheet 1 contains a list of employee names.
Problem: my formula only returns if true of false, hence if the employee name in the text is found in sheet 1 or not. But i would like to return the employee name instead of true of false.
Hope that is clear. Thank you in advance.
Actually it is as follows sorry:
A1: Airplane ticket John Smith NYC-LAX
B2: $650.-
C3: =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet1!$A$1:$A$42;A2)))>0
Hello David!
If I understand your task correctly, the following formula should work for you:
=IFERROR(INDEX(Sheet1!$A$1:$A$42, MATCH(1,IF(IFERROR(SEARCH (Sheet1!$A$1:$A$42,A1,1),0)>0,1,0),0)),"")
where A1: Airplane ticket John Smith NYC-LAX
Sheet1!$A$1:$A$42 - is list of employee surnames
Hope this is what you need.
Thanks a lot Alexander, on a first try I could not make it work, i will try again later when I have my head free.
I AM TRYING TO FIND IF A CHARACTER IS IN A STRING AND RETURN A TRUE OR FALSE. IF THE STRING CONTAINS * THE SEARCH WOULD RETURN TRUE. FOR EXAMPLE:
PREMARIN 0.625 MG TAB 00046110281 TAB 90.0000 FALSE
SYNTHROID* 100 MCG TAB 00074662490 TAB 843.0000 TRUE
Hello Vicki!
Please try the following formula:
=IF(SEARCH("~*",A1,1),TRUE,FALSE)
Hope this is what you need.
Hello Sir
Thanks a lot for your great work. I have a query if you kindly help.
I have list of products which has very long name, I want to split them either by first , or - or : if found none of them then will return back original product name.
example of product name:
1.PLUSINNO Telescopic Fishing Rod and Reel Combos Full Kit, Spinning Fishing Gear Organizer
2.KastKing Sharky III Fishing Reel - New Spinning Reel - Carbon Fiber 39.5 LBs Max Drag
3.Rippin Lips Super Cat Casting Rod with Glow Tip:7-Feet 6-Inch, Medium-Heavy
4.ABU GARCIA AMBASSADEUR C3 ROUND REEL
expected result:
1.PLUSINNO Telescopic Fishing Rod and Reel Combos Full Kit
2.KastKing Sharky III Fishing Reel
3.Rippin Lips Super Cat Casting Rod with Glow Tip
4.ABU GARCIA AMBASSADEUR C3 ROUND REEL
Thank You.
Hello Mizanur!
If I understand your task correctly, please try the following formula:
=LEFT(A1,MIN(IFERROR(FIND({",",";","–",":"}, A1,1),LEN(A1)))-1)
We have a ready-made solution for your task. I'd recommend you to have a look at our Ablebits Tools - Extract Text and Remove Characters.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
I hope it’ll be helpful.
(585*205)*1*0.082
sir i want to extract 585 in separate column and 205 in separate column and 1 in separate column and also digit numbers in all cases increase or decrease like 5850 or 58 and 20 or 2050 and 10 or 100so pelase help me for find forumale with mid function or any other
Hello Sonu!
If I understand your task correctly, to extract the first digit from your expression, use the formula
=LEFT(SUBSTITUTE( SUBSTITUTE(A1,"(",""),")",""), FIND("*", SUBSTITUTE( SUBSTITUTE(A1,"(",""),")",""))-1)
To extract the second digit -
=MID(E1,FIND("*",SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""))+1, FIND("*",SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""), (FIND("*",SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),1)+1)) - FIND("*",SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""))-1)
To extract the third digit -
=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"*","@",2), FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"*","@",2))+1, FIND("$",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"*","$",3))- FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"*","@",2))-1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Good morning,
I have a situation where I have a daily update on names that all need to have the company name filled in in the next column. As many names occur more than once, I have created a source list with names and companies and want to create an IF(FIND) function to automatically populate the company names for names for which the company is known.
I'm struggling with the second part of my formula. This is what I have so far:
=IF(FIND(F420;'Company Source'!$A$2:$A$1793);;G420) - whereas F420 is a name, and $A$2:$A$1793 is the list of names and G420 is where the company name needs to be. Once that name is found, how do I match the company name in the source list to where I need it to be?
I hope this makes sense.
Thank you for your help.
Geert
Hello Geert!
If you want to automatically enter a company name in a cell from a certain list, I recommend using an Excel drop-down list in this case. For more details on how to create it, please read here.
However, you’re saying that you enter a name in F420, and the company name is inserted into G420. In this case you’d better use the VLOOKUP function:
=VLOOKUP(F420,’Company Source’!$A$2:$A$1793),1,0)
If you have additional information about companies which is located, for example, in the cell ’Company Source’!$B$2:$B$1793, you can automatically pull it and insert in cell G421 using this formula:
=VLOOKUP(F420,’Company Source’!$A$2:$B$1793),2,0)
Please find more details on how to use VLOOKUP here.
If you have any other questions, I’ll be happy to answer them.
How can I use the function with OR to find "ORDER" as well? at this time it only on the proper Alphabet
=IFERROR(IF(FIND("Order",C82),"023"),"000")
Hello Navdeep!
To make the results of your search in text values case insensitive, please use the SEARCH function instead of the FIND one:
=IFERROR(IF(SEARCH("Order",C82),"023"),"000")
I hope this will help, otherwise please do not hesitate to contact me anytime.
ID NO NAME OF CUSTOMER BIX ID
1 KARUTURI PRASANNA 123456
2 KORANA PADMA 134251
3 KANTIPUDI PRASAD 124561 HOW TO FIND OUT SEARCH BY NAME
Hello Prasad!
I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. In that case I will try to help you.
Hi,
I am trying to extract only the amount from the following but facing some issues.
Spot Award (INR 5,000)
I only want the amount of 5000 to be removed and reflect separately in a column, without the INR. The original column should just reflect Spot Award. Could you help with this?
Hello Kavya!
You can extract the number from the text using this formula:
=SUBSTITUTE(MID(A1, FIND("INR",A1,1)+4, LEN(A1) - FIND("INR",A1,1)-4),",","")
The formula result will also be text.
If it is necessary to convert it to number, please insert two “minus” signs before the first function name in the formula:
=--SUBSTITUTE(MID(A1, FIND("INR",A1,1)+4, LEN(A1) - FIND("INR",A1,1)-4),",","")
I hope this will help, otherwise please do not hesitate to contact me anytime.
1,83,5,White,Barry,Solihull & S H,10:00.14,,10:00.14,,,13:48:19.69,,,,10:00.14,10:00.14,,
What formulas do I need to put in to put every other bit into its own cell?
I have formulas to put the first 3 in i.e 1 and 83 and 5 go into their own cell. How can i get the rest to do it with a formula?
Guessing you got an answer by now, but I suggest using the Text to Columns' function instead of a formula
Hi forum,
[{"id"=>2445045452, "wid"=>"XI53FEA", "order_item_id"=>"11803289568455100", "order_item_unit_id"=>nil, "quantity"=>1, "shipment_id"=>1709774212, "status"=>"CONFIRMED", "external_order_id"=>"OD118032895684551000", "customer_return_reason"=>nil}] in this data how i will extract/lookup the order_item_id in excel. kindly do the needful.
Assuming your input is stored in cell 'A1'; the formula stated below will help you extracting the desired information.
=MID(A1,FIND("order_item_id",A1)+LEN("order_item_id")+4,(FIND(", ",A1,FIND("order_item_id",A1)+LEN("order_item_id")+4))-(FIND("order_item_id",A1)+LEN("order_item_id")+4)-1)
Hi Forum
I have a text cell which can contain more values.I am looking for a formula that can determine if for instance the text contans the Word "RED", if so the formula result should state "RED", at the same time I need to search for the word "GREEN" and the result should state "GREEN". IF neither "RED" og "GREEN" is in the text cell it should give the result "GREY". Neither RED og GREEN can be in the text cell at the same time.
How do I do that I have tried with search and IF sentences but it will fails when I get to the second search in the formula.
I think this should work.
=IF(ISERROR(SEARCH("red",A1)),IF(ISERROR(SEARCH("green",A1)),"GREY","GREEN"),"RED")
Hi there, this is really helpful thank you. How would I add a this search in to this?For example if I also wanted to search for BLUE? Thank you!
I want to find continues absent greater than 4 days like below
Amit AAAAAPPPPPPA Yes
Arun PAPPAPPPAPA NO
HI All,
look for guidance for I want find 1st character what the formula will be if my column got 2 criteria which is I want only show help what my formula will be?
1. help_request 123/123
2. help request 123/123
How to find Word'd position in string in Cell in Excel?
Example:
India, UK, USA, RSA (in A1)
UK (in B1)
Expected result in A2 2.
Where 2 is the position of word in A1. Like if RSA in B1 the answer should 4.
Hi Guys, my questions is straight forward:)
I have a column that has lots of sets of numbers but i want to extract only numbers that starts with 7 from left.
Thanks
Abdel
Filter with 7*
Dear, I am looking for the solution of occurrence of a repeated characters in a cell. I have data of contact numbers, like 01150000011, 01171222229, 01129999917 etc. I want to identify cells that have 5 or more than 5 consecutive numbers in phone number ( like 0 five times in first phone, 2 five times in 2nd phone and 9 six times in 3rd phone).
Hi,
This was to solve on the 60,000 data ,
Thanks & regards
Hi sir,
My name Niru,
My query is latest 10 days sales
Use Excel formulas.
Pass
Fail
Fail
Fail
Fail
I need "Pass" text separate from above list
Hi and thank you for the article.
Has anyone a suggestion on how to use a whole named range as the Search or Find Value?
Best, Phil
I currently have a formula that returns the last letter of T using this function:
=RIGHT(E2,LEN(E2)-SEARCH("-",E2)-4)
I then filter the data from there.......
I would like to search for multiple characters such as P, O , T.
Is there a function I can use to search for a set of letters without using advance filters?
Do you recommend just using advance filtering, if so what do I put into the criteria to find the numbers with Multiple characters.
B003-0144T
B003-0144P
B003-0144P
B003-0144P
B003-0144O
B003-0144O
B003-0144O
B003-0144E
B003-0144A
Thanks,
Willie
I currently have a formula that returns the last letter of T using this function:
=RIGHT(E2,LEN(E2)-SEARCH("-",E2)-4)
TB003-0144T
i got to say, your explanations provided me with a better understanding of the formula i was looking for. incase someone else is losing head over the same, below is a formula for returning text begining with "P" but ending before either " " or "-" =IF((SEARCH("-",C2))>(SEARCH(" ",C2,5)),MID(C2,SEARCH("P",C2),SEARCH(" ",C2,5)-SEARCH("p",C2)),MID(C2,SEARCH("P",C2),SEARCH("-",C2)-SEARCH("p",C2)))
Hi,
In the above example 3 "Extract N characters following a certain character", i see there is 3rd argument formulae, similar to that i have (4th and last argument) and (4th, 5th and last argument).
Ex 1: 123-345-456-78-46
Ex 2: 234-793-42-856-674-123
Ex 3: 123-345-456-789
i have numerous recrods/rows. i would like to split after "-" into each cell. I have tried to split using text to columns it worked out. But i would like to use the formulaes similar to example 3.
Can anyone help me to split the data, please.
Hello,
I have a excel sheet of attendance, For Example Employee Name:- Ajay Kumar Time :-09:06 J.p Site
And i want to create a new excel sheet which link with our attendance sheet . for example whenever Ajay kumar present on site So our new sheet will automatically appear in the present. What would the Formula be for this? Thank you.
Present Attendance Sheet
1. Ajay Kumar 09:06(J.P SITE)
New Attendance Sheet
1. Ajay Kumar (P) Show only present
I am trying to copy just the characters between the 2nd space and the 4th space. For instance, for the following cells, I want only "Lebron James" and Giannis Antetekounmpo" and Nikola Jokic". What would the formula be for this? Thank you.
L. James LeBron James SF/PF
G. Antetokounmpo Giannis Antetokounmpo SF/PF
N. Jokic Nikola Jokic C
Hello!
I would like to convert a string "John Watson Dalton" to "jwdalton". Can you help me?
Question? What formula could I use to search for a number value in a cell and give me the text description in the cell next to it
Lets say
A1= 8810 B2= Clerical
How do I create a formula to search for the number 8810 and bring up the Clerical description?
Amber:
I think you should try an approach that uses VLOOKUP.
The description and examples are here on AbleBits at:
https://www.ablebits.com/office-addins-blog/excel-vlookup-tutorial/
I have a full column of text with alphabetic as well numeric data, E.g 428538, 42H11, 225FL560, so what i want to do is if the text doesn't contain any Alphabet then I will take the last 2 numeric values as my req. data, but if the text contains alphabet then the letters needed are all letters before that as for 42H11=42, and for 225FL560=225, but also if the number before the alphabet are having more than 2 digits then the data needed is in the form of /10 so, e.g I will be needing output from 225FL560 as 22.5 and from 42H11 as 42, kindly help me..... thanks in avance...
There is a sentences in cell so how can v find number value in that sentence
Example:- i want to buy apple fruits of cost $50 per piece so I want retrieve only $50 from cell by using formulae
Mukesh:
Why not use the Text-to-Columns tool and set the 32 GB into its own column?
Hi
My Name is Mukesh Singh
Sir I have a query
Apple iPhone 6 32 GB Gold
I want "32 GB" in a separate cell using formula
Please help me to solve
Hi there,
I'm trying to fill in some missing data in my excel sheet. The method I am using is substitution where I have 6 columns. For example:
(Day) (Hour) (Wind direction) (wind speed) (stability) (rain in inches)
If I am missing the wind direction and wind speed I will look 2 weeks prior or after the missing data point for the same hour and stability and substitute those values in for the missing values. I used vlookup before and it would substitute from the top of the column or bottom but never the values closest from the missing data. My question is, is there a better method than the vlookup, would the find or search functions work? Or is there a method for vlookup to start from the missing data point and work away rather than starting from the top or bottom of the 2 week gap?
In my target string, the substring i want to extract is of the form Mxx - the letter M followed by a two digit number. THe target string will only have one occurrence of such a substring. The position of it though, is not fixed. For example:
F_8_M01_MASTER_MARKS_V15 - I am looking for M01.
F_8_M17_PTLY_XTV_V6 - I want to extract M17.
KB1F_MESURE_M34_FIN_V01.2 - I want to extract M34
How can I do that?
DesperatelyNeedHelp:
Here's a formula that will search a string and return the value you enter into a cell.
You can do it two ways I'll show you both and you can decide which one works best for you.
OK, you can enter the search value directly into the formula like this;
Where F_8_M17_PTLY_XTV_V6 is in B13 enter this formula into an empty cell.
=IF(B13="","",IF(ISNUMBER(SEARCH("M17",B13)),"M17"))
Alternatively you can enter the search value into a cell and reference that cell in the formula like this:
=IF(B13="","",IF(ISNUMBER(SEARCH(D2,B13)),D2))
Where F_8_M17_PTLY_XTV_V6 is in B13 and you enter M17 into D2 this will return M17 into the cell where this formula is.
i want to find a text 'P' in a string which is then followed by 3 numbers. The 3 numbers can be anything.
If I understand your question this formula should work.
Where "P123" is in A44 enter this in an empty cell:
=MID(A44,FIND("P",A44),1)
col A contains some set of strings, Col B contains some Sub-strings , and the corresponding coloumn C should return values of string which contains the particular substring.
what formula can be used to pull out the string which contains this particular sub-string
Col A Col B col C
ABCD-alpha rain rainrain-rhyme
1234-num abc ABCD-alpha
rainrain-rhyme numb 1234-num
Lavanya:
Here's a formula that works if there are no duplicates,
=IFERROR(INDEX($A$1:$A$5,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(B1,$A$1:$A$5)),,),0)),"No Match")
Just copy this down the C column and it will return the string from the A column that contains the sub-string found in the B column.
You can expand the $A$1:$A$5 range. The B1 will change as you copy it down the C column.
FYI, the "numb" in your sample will return the "No Match".
Absolutely the best post that I found on this subject!
This is comprehensive and detailled, yet very clear and easy to understand. The choice of examples just adds to that.
Thank you very much, this post was truly helpful.
Kindest regards
Pieter
Hi,
I have a problem in excel for find and pest,
The problem is "in a excel one page i have some numbers(123456), same page same number included in some words(abcd123456), so i can find out based on any formula please let me know, i am waiting.
Hello,
I am trying to figure out how to do the following:
Count the number of cells which have the character + within the word.
Each of over 60 columns will have a different word.
E.g. BOB+CAT, DOG+BIRD, CAN+, etc.
Count Number of Cells That Contain Specific Character
=COUNTIF(A23:A29,"*+*")
Where A23:A29 is the range of cells you want to count the number of times the “+” character is in the text.
hello madam..
good morning..
we want some help from you..
we just want to confirm why we use find and replace formula...
we use find and replace formula from home menu or by pressing(ctrl+f)..