Excel FIND and SEARCH functions with formula examples

The tutorial explains the syntax of the Excel FIND and SEARCH functions and provides formula examples of advanced non-trivial uses.

In the last article, we covered the basics of the Excel Find and Replace dialog. In many situations, however, you may want Excel to find and extract data from other cells automatically based on your criteria. So, let's have a closer look at what the Excel search functions have to offer.

Excel FIND function

The FIND function in Excel is used to return the position of a specific character or substring within a text string.

The syntax of the Excel Find function is as follows:

FIND(find_text, within_text, [start_num])

The first 2 arguments are required, the last one is optional.

  • Find_text - the character or substring you want to find.
  • Within_text - the text string to be searched within. Usually it's supplied as a cell reference, but you can also type the string directly in the formula.
  • Start_num - an optional argument that specifies from which character the search shall begin. If omitted, the search starts from the 1st character of the within_text string.

If the FIND function does not find the find_text character(s), a #VALUE! error is returned.

For example, the formula =FIND("d", "find") returns 4 because "d" is the 4th letter in the word "find". The formula =FIND("a", "find") returns an error because there is no "a" in "find".
Excel FIND function

Excel FIND function - things to remember!

To correctly use a FIND formula in Excel, keep in mind the following simple facts:

  1. The FIND function is case sensitive. If you are looking for a case-insensitive match, use the SEARCH function.
  2. The FIND function in Excel does not allow using wildcard characters.
  3. If the find_text argument contains several characters, the FIND function returns the position of the first character. For example, the formula FIND("ap","happy") returns 2 because "a" in the 2nd letter in the word "happy".
  4. If within_text contains several occurrences of find_text, the first occurrence is returned. For example, FIND("l", "hello") returns 3, which is the position of the first "l" character in the word "hello".
  5. If find_text is an empty string "", the Excel FIND formula returns the first character in the search string.
  6. The Excel FIND function returns the #VALUE! error if any of the following occurs:
    • Find_text does not exist in within_text.
    • Start_num contains more characters than within_text.
    • Start_num is 0 (zero) or a negative number.

Excel SEARCH function

The SEARCH function in Excel is very similar to FIND in that it also returns the location of a substring in a text string. Is syntax and arguments are akin to those of FIND:

SEARCH(find_text, within_text, [start_num])

Unlike FIND, the SEARCH function is case-insensitive and it allows using the wildcard characters, as demonstrated in the following example.

And here's a couple of basic Excel SEARCH formulas:

=SEARCH("market", "supermarket") returns 6 because the substring "market" begins at the 6th character of the word "supermarket".

=SEARCH("e", "Excel") returns 1 because "e" is the first character in the word "Excel", ignoring the case.

Like FIND, Excel's SEARCH function returns the #VALUE! error if:

  • The value of the find_text argument is not found.
  • The start_num argument is greater than the length of within_text.
  • Start_num is equal to or less than zero.

Excel SEARCH function

Further on in this tutorial, you will find a few more meaningful formula examples that demonstrate how to use SEARCH function in Excel worksheets.

Excel FIND vs. Excel SEARCH

As already mentioned, the FIND and SEARCH functions in Excel are very much alike in terms of syntax and uses. However, they do have a couple of differences.

1. Case-sensitive FIND vs. case-insensitive SEARCH

The most essential difference between the Excel SEARCH and FIND functions is that SEARCH is case-insensitive, while FIND is case-sensitive.

For example, SEARCH("e", "Excel") returns 1 because it ignores the case of "E", while FIND("e", "Excel") returns 4 because it minds the case.
Case-sensitive FIND vs. case-insensitive SEARCH

2. Search with wildcard characters

Unlike FIND, the Excel SEARCH function accepts wildcard characters in the find_text argument:

  • A question mark (?) matches one character, and
  • An asterisk (*) matches any series of characters.

To see how it works on real data, consider the following example:
Search with wildcard characters in Excel

As you see in the screenshot above, the formula SEARCH("function*2013", A2) returns the position of the first character ("f") in the substring if the text string referred to in the within_text argument contains both "function" and "2013", no matter how many other characters there are in between.

Tip. To find an actual question mark (?) or asterisk (*), type a tilde (~) before the corresponding character.

Excel FIND and SEARCH formula examples

In practice, the Excel FIND and SEARCH functions are rarely used on their own. Typically, you would utilize them in combination with other functions such as MID, LEFT or RIGHT, and the following formula examples demonstrate some real-life uses.

Example 1. Find a string preceding or following a given character

This example shows how you can find and extract all characters in a text string to the left or to the right of a specific character. To make things easier to understand, consider the following example.

Supposing you have a column of names (column A) and you want to pull the First name and Last name into separate columns.

To get the first name, you can use FIND (or SEARCH) in conjunction with the LEFT function:

=LEFT(A2, FIND(" ", A2)-1)

or

=LEFT(A2, SEARCH(" ", A2)-1)

As you probably know, the Excel LEFT function returns the specified number of left-most characters in a string. And you use the FIND function to determine the position of a space (" ") to let the LEFT function know how many characters to extract. At that, you subtract 1 from the space's position because you don't want the returned value to include the space.

To extract the last name, use the combination of the RIGHT, FIND / SEARCH and LEN functions. The LEN function is needed to get the total number of characters in the string, from which you subtract the position of the space:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

or

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

The following screenshot demonstrates the result:
Splitting the first name and last names into separate columns.

For more complex scenarios, such as extracting a middle name or splitting names with suffixes, please see How to split cells in Excel using formulas.

Example 2. Find Nth occurrence of a given character in a text string

Supposing you have some text strings in column A, say a list of SKUs, and you want to find the position of the 2nd dash in a string. The following formula works a treat:

=FIND("-", A2, FIND("-",A2)+1)

The first two arguments are easy to interpret: locate a dash ("-") in cell A2. In the third argument (start_num), you embed another FIND function that tells Excel to start searching beginning with the character that comes right after the first occurrence of dash (FIND("-",A2)+1).

To return the position of the 3rd occurrence, you embed the above formula in the start_num argument of another FIND function and add 2 to the returned value:

=FIND("-",A2, FIND("-", A2, FIND("-",A2)+1) +2)
FIND formulas to find the position of 2nd and 3rd occurrences of a specific character in a string

Another and probably a simpler way of finding the Nth occurrence of a given character is using the Excel FIND function in combination with CHAR and SUBSTITUTE:

=FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3))

Where "-" is the character in question and "3" is the Nth occurrence you want to find.

In the above formula, the SUBSTITUTE function replaces the 3rd occurrence of dash ("-") with CHAR(1), which is the unprintable "Start of Heading" character in the ASCII system. Instead of CHAR(1) you can use any other unprintable character from 1 to 31. And then, the FIND function returns the position of that character in the text string. So, the general formula is as follows:

FIND(CHAR(1),SUBSTITUTE(cell,character,CHAR(1),Nth occurrence))

At first sight, it may seem that the above formulas have little practical value, but the next example will show how useful they are in solving real tasks.

Note. Please remember that the Excel FIND function is case-sensitive. In our example, this makes no difference, but if you are working with letters and you want a case-insensitive match, use the SEARCH function instead of FIND.

Example 3. Extract N characters following a certain character

To locate a substring of a given length within any text string, use Excel FIND or Excel SEARCH in combination with the MID function. The following example demonstrates how you can use such formulas in practice.

In our list of SKUs, supposing you want to find the first 3 characters following the first dash and pull them in another column.

If the group of characters preceding the first dash always contains the same number of items (e.g. 2 chars) this would be a trivial task. You could use the MID function to return 3 characters from a string, starting at position 4 (skipping the first 2 characters and a dash):

=MID(A2, 4, 3)

Translated into English, the formula says: "Look in cell A2, begin extracting from character 4, and return 3 characters".
The MID formula to extract 3 characters following a dash

However, in real-life worksheets, the substring you need to extract could start anywhere within the text string. In our example, you may not know how many characters precede the first dash. To cope with this challenge, use the FIND function to determine the starting point of the substring that you want to retrieve.

The FIND formula to return the position of the 1st dash is as follows:

=FIND("-",A2)

Because you want to start with the character that follows the dash, add 1 to the returned value and embed the above function in the second argument (start_num) of the MID function:

=MID(A2, FIND("-",A2)+1, 3)

In this scenario, the Excel SEARCH function works equally well:

=MID(A2, SEARCH("-",A2)+1, 3)
Use the FIND function to determine the starting point of the substring you want to extract.

It's great, but what if the group of chars following the first dash contains a different number of characters? Hmm... this might be a problem:
The group of chars following the first dash contains a different number of characters

As you see in the above screenshot, the formula works perfectly for rows 1 and 2. In rows 4 and 5, the second group contains 4 characters, but only the first 3 chars are returned. In rows 6 and 7, there are only 2 characters in the second group, and therefore our Excel Search formula returns a dash following them.

If you wanted to return all chars between the 1st and 2nd occurrences of a certain character (dash in this example), how would you proceed? Here is the answer:

=MID(A2, FIND("-",A2)+1, FIND("-", A2, FIND("-",A2)+1) - FIND("-",A2)-1)
The FIND formula to return all characters between the first and second occurrences of a specific character

For better understanding of this MID formula, let's examine its arguments one by one:

  • 1st argument (text). It's the text string containing the characters you want to extract, cell A2 in this example.
  • 2nd argument (start_position). Specifies the position of the first character you want to extract. You use the FIND function to locate the first dash in the string and add 1 to that value because you want to start with the character that follows the dash: FIND("-",A2)+1.
  • 3rd argument (num_chars). Specifies the number of characters you want to return. In our formula, this is the trickiest part. You use two FIND (or SEARCH) functions, one determines the position of the first dash: FIND("-",A2). And the other returns the position of the second dash: FIND("-", A2, FIND("-",A2)+1). Then you subtract the former from the latter, and then subtract 1 because you don't want to include either dash. As the result, you will get the number of characters between the 1st and 2nd dashes, which is exactly what we are looking for. So, you feed that value to the num_chars argument of the MID function.

In a similar fashion, you can return 3 characters after the 2nd dash:

=MID(A2, FIND("-",A2, FIND("-", A2, FIND("-",A2)+1) +2), 3)

Or, extract all the characters between the 2nd and 3rd dashes:

=MID(A2, FIND("-", A2, FIND("-",A2)+1)+1, FIND("-",A2, FIND("-", A2, FIND("-",A2)+1) +2) - FIND("-", A2, FIND("-",A2)+1)-1)
FIND formulas to extract three or all the characters between the 2nd and 3rd dashes

Example 4. Find text between parentheses

Supposing you have some long text string in column A and you want to find and extract only the text enclosed in (parentheses).

To do this, you would need the MID function to return the desired number of characters from a string, and either Excel FIND or SEARCH function to determine where to start and how many characters to extract.

=MID(A2,SEARCH("(",A2)+1, SEARCH(")",A2)-SEARCH("(",A2)-1)

The logic of this formula is similar to the ones we discussed in the previous example. And again, the most complex part is the last argument that tells the formula how many characters to return. That pretty long expression in the num_chars argument does the following:

  • First, you find the position of the closing parenthesis: SEARCH(")",A2)
  • After that you locate the position of the opening parenthesis: SEARCH("(",A2)
  • And then, you calculate the difference between the positions of the closing and opening parentheses and subtract 1 from that number, because you don't want either parenthesis in the result: SEARCH(")",A2)-SEARCH("(",A2))-1

Excel SEARCH formula to find and extract text between parentheses

Naturally, nothing prevents you from using the Excel FIND function instead of SEARCH, because case-sensitivity or case-insensitivity makes no difference in this example.

Hopefully, this tutorial has shed some light on how to use SEARCH and FIND functions in Excel. In the next tutorial, we are going to closely examine the REPLACE function, so please stay tuned. Thank you for reading!

Download practice workbook

FIND and SEARCH formula examples

Table of contents

440 comments

  1. My post may be neither necessary nor useful, just my subjective feedback..
    Nevertheless, I need to thank Svetlana very much for the well-arranged, almost analytical tutorials, which have helped me a lot in my work, as well as this one right now.

    Thanks a lot, Kind Regards.

    • Hi Jaroslav,

      Thank you so much about your kind words about my work. I'm delighted to hear that our tutorials are useful to you!

  2. Hi,

    I would really appreciate if you can help me. I have a list of 150 popular words (A1: A150). I have a set of 500 customers' comments (B1: B500). I want to write a function that for each customer comment, excel searches for the occurrence of any of the 150 popular words (A1: A150) and returns the frequency of their occurrence in a cell in front of each customer.

    For example, if I have 4 words (good, tasty, quite, fresh, like), and customer A said: "the meal was very tasty and fresh. I like the way food is presented. I also like the colours used in the place".
    In this comment, tasty used 1 time; fresh used 1 time; like used twice.

    I want the function to calculate the occurrence of these words as 4 times. I don't care about how many each word is repeated. I just need the total. I also want the function to be case insensitive since some customer might use capital case letters and other use lower case letters.

    I used the following formula to count the frequency of one word, but can't adopt it to search for the 150 words in the word list I have in A1:A150

    C1=ISNUMBER(SEARCH("like",B1))

    I really appreciate your help.

    • I would really appreciate if you can help me with this request. I am struggling to work it out.

  3. I have a query where I need to find any punctuation characters in a list of names (where they have accidentally typed something wrong). It makes sense to repeat the formula for each character I am trying to find which is fine but all find and search formulas seem to only search one cell and I'd like to put a range in there. Is there something I can use to search the whole range? or even a way of making the LEN() formula search a range as that would do the job. thanks

  4. Sir/Mam,

    In excel, i have a bundle of statements(A2) and other cell i have specific values(B2) with pipelines as separators within the value. Now, what formula to be used to compare the specific values if present in the statement or not and the output to highlight the value in the specific cell - B2

    • Hello!
      Your task is not completely clear to me. Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  5. hi, i would like to extract data from 1 sheet to other sheet .Like example , status have 3 input there, that is completed, cancel, shipping , i wan extract all the data regarding shipping. but i just can get 1 data of shipping. how can i do with extract all data regarding it

      • I had tried the method but it direct show with wrong
        this is what i type :
        =IFERROR(INDEX(orders!$A$2:A,SMALL(IF($A$16=orders!$B$2:B,ROW(orders!$A$2:A)-MIN(ROW())+1,""),ROW()-1)),"wrong")

        • Hello!
          Unfortunately, without seeing your data it is impossible to give you advice.

          I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

          We'll look into your task and try to help.

  6. The column AG contains the combination of below, need to separate seconds, minute, hour,day,week, months in different column.

    0s
    32m 14s
    3h 21m 18s
    1w 1d 12h 0m 28s
    6d 18h 36m 50s
    1mos 2w 2d 9h 6m 56s

    please help me with formula

      • Please refer my expected result in Excel, kindly assist me, alignment is not correct.

        Resolution Time Months Weeks Days Hours Minutes Seconds
        0s 0 0 0 0 0 0
        13m 17s 0 0 0 0 13 17
        1h 48m 15s 0 0 0 1 48 15
        4d 9h 27m 40s 0 0 4 9 27 40
        1w 6d 7h 24m 12s 0 1 6 7 24 12
        6d 16h 17m 38s 0 0 6 16 17 38
        6h 13m 34s 0 0 0 6 13 34
        1w 6d 7h 24m 12s 0 1 6 7 24 12
        9h 0m 0s 0 0 0 9 0 0
        1mos 2w 2d 9h 6m 56s 1 2 2 9 6 56

        • Hello!
          The formula below will do the trick for you:

          =REPT("0 ",5-(LEN(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(H1,ROW($1:$93),1)), MID(H1,ROW($1:$93),1)," ")))," "," ")) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(H1,ROW($1:$93),1)), MID(H1,ROW($1:$93),1)," ")))," "," ")," ","")))) & SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(H1,ROW($1:$93),1)), MID(H1,ROW($1:$93),1)," ")))," "," ")

          Divide the resulting text into columns, as I advised you earlier.
          I'd recommend you to have a look at our Ablebits Tools - Split text tool that can help you split the text to columns. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

          • tried the free tool, but not getting the expected result. Formula applied in one column, need to be in separate column.

  7. Hi, Would like to ask you help for this scenario below:
    For example I have this data below in a column, and I want to search for which rows have "John" only in their name.
    1.Johnson New
    2.Jane John
    3.Earl John Watson
    4.Amy Anne John

    With the data in Row 1, it returns a value for it because it contains "John" in "Johnson" but I won't be needing that because I need only the "John" name.
    How can I do this in a formula? Thanks!

    • adding more data:

      5.John Watsons
      6.John Johnsons

    • Hello!
      To find an exact match of a word in the text, you can use this formula:

      =IF((ISNUMBER(FIND($A$1&" ",A2))+ISNUMBER(FIND(" "&$A$1,A2))+($A$1=A2))>0,TRUE,"")

      where cell A1 contains the word that we are looking for in the text.
      I hope my advice will help you solve your task.

  8. I read through your article and most of the questions but haven't found a way of removing multiple instances that occur in a single cell. When I import a data table of projects with employee names and occupations from our system the information comes into an Excel table with the project number as the row and the various occupations as the columns. The names are displayed with system generated characters and id numbers.

    For example for project manager the name of Sue Smith shows in the table as Smith,Sue;#56789. For occupations with a single entry I can use left and search functions to remove the";#56789" using =LEFT(A1,SEARCH(";",A1)-1). The problem comes in when I get multiple people for a occupation. For example for engineer I may get Jones, John;#1234;#Trifuntov,Alexander;#789;#Sharashova,Natalia;#90. If I use the above formula all I get is Jones, John and the other names are truncated. I am looking to have it come up as Jones, John;Trifuntov, Alexander;Sharashova,Natalia I have tries using REPLACE and SUBSTITUTE but due to the varying number of people, length of names and id numbers I keep getting issues. Is there any way to use a find/search formulas?

    • Hello!
      For your example text, you can use the formula

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER(--MID(A1,ROW($1:$91),1))),MID(A1,ROW($1:$91),1),"")))," ",""),"#;#",""),";#","")

      It removes from the text all numbers and extra characters "#" and ";"
      Hope this is what you need.

      • I really appreciate your fast response. When I use the above formula I get an error. Maybe we can start simple and once I get that correct I can expand. Let's say

        Jones, John;#1234;#Trifuntov,Alexander;#789;#Sharashova,Natalia;#90 is in cell A1

        and I want to look like

        Jones, John; Trifuntov,Alexander; Sharashova,Natalia in cell B1.

        What would the formula be in cell B1?

        Again thanks and I appreciate your patience!

  9. Hello,
    I would very much appreciate any suggestions/guidance to find a specific text, "Apple," in the middle and at the end where there is no space in a cell. (Yes.. I am sorry and am feeling bad to have to ask this ignorant question as I am a new user.)

    Here's my formula to find the text "Apple" in the middle.
    =MID(A1,FIND("#Apple",A1,1)+1,(FIND(";",A1,FIND("#Apple",A1)))-(FIND("#Apple",A1,1)+1))

    Column A
    A1 - 15;#Apple;#121;#Pears - Organic;#18;#Banana;#87;#Strawberry;#38;Cherry;#149;#Orange
    A2 - 18;#Banana;#87;#Strawberry;#38;#Cherry;#15;#Apple;#121;#Pears - Organic
    A3 - 16;#DragonFruit;#121;#Pears - Organic;#18;#Banana;#87;#Strawberry;#38;Cherry;#15;#Apple

    Column B with the MID Formula
    B1 - Apple
    B2 - Apple
    B3 - #VALUE <-- because Apple is at the end

    I need a formula that would get both middle and end....

    Appreciate any suggestions/guidance.

    Kind regards,
    Miko

    • Hello Miko!
      If you just need to extract "Apple" from the text, then you can just take 5 characters:

      =MID(A1,FIND("#Apple",A1,1)+1,5)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      • I cannot thank you enough. You saved hours of my time to prepare a weekly report out of crude SharePoint datadump.
        Gratefully,
        Miko

  10. Hi,
    I would like to search for a specifik text within a column. Example: If a cell cointains: "The upper side. System:20 Tag:20A-VA004" I would like to copy the tag number (Tag:20A-VA004) to a new cell, both tag written TAG, tag: and Tag. I have tried the formula =TRIM(LEFT(SUBSTITUTE(MID(C12;FIND("Tag:";C12);LEN(C12));" ";REPT(" ";100));100)), but this does not include space and different writings. Can you please help?
    Regards, Kristin

    • Hello!
      If I understand your task correctly, to extract text starting at some position, use the formula

      =MID(D1,SEARCH("tag",D1,1),LEN(D1)-SEARCH("tag",D1,1)+1)

      I hope it’ll be helpful.

  11. Hi,

    I need to extract number from strings like:
    #34224 some text
    #43543543: some text
    #34243-some text
    #3423145
    so number can be different in length and be followed by space OR semicolon OR dash OR do not have any character after.
    the formula that I have for the third case is =IFERROR(NUMBERVALUE(IF(SEARCH("#";[@Comment])=1;MID([@Comment];2;SEARCH("-";[@Comment])-2);FALSE));0)
    but how to modify it to include all 4 cases?

  12. Good day! I am struggling to work on this one. I need to mark the 50th word in a paragraph with * (still with the whole paragraph, im not just gonna extract the 50th word out) but if it is equal or less then 50 words, then I can just copy the text as it is. How can I do this? Thank you for your help!

    • Hello!
      If I understand your task correctly, to put * before the 50th word, use the formula

      =IF(LEN(C1)-LEN(SUBSTITUTE(C1," ",""))<51,LEN(C1)-LEN(SUBSTITUTE(C1," ","")),SUBSTITUTE(C1," ","*",50))

      I hope this will help

  13. Respected Sirs,

    Here I have a problem to solve
    in a coloumn of name there are three or two words name
    Ram Bahadur Gautam
    sanjay Limbu

    I need to extract only last sir name to another column and copy the formula
    Thank your for your kindly assistance
    Sanay limbu

    • Hello!
      If I got you right, the formula below will help you with your task:

      =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),20))

      This will help to extract the last word from the text.

  14. Hi Alex! Great tutorials! I lead a group of sales coaches which write their feedback in an excel sheet. I am trying to build a formula which searches a keyword or phrase within a paragraph and the word or phrase after it ( Ex. keyword:OPPORTUNITY: FACT FINDING) Then, I would keep track how many times the phrase after the keyword was used. Any thoughts?

    • Hello Juan!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail.Is the feedback written in several cells or in one? Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

  15. Cell A1 contains the googlefinanceticker NASDAQ:TQQQ
    Cell B1 should contain a Formula wich verifies if A1 contains a colon. If YES than got the name of the ticker.
    The idea should be like this, but I have problems with combination of funktion and syntaxis.
    =IF(ISNUMBER(FIND(":";P2));AA2=GOOGLEFINANCE(AP2;"PRICE");"ist kein googlefinanceID")
    Any idea !!! Thanks in advance for your help,
    Valentin

  16. i want to use =RİGHT operation and every word contain ")" for ex asdasdasd),asdasdewqd),uymjutynm), but thera are a so many blanks and i want to delete blanks and i want to assigned them a number for exam. asdasdasd) = 123 asdasdewqd)=432 uymjutynm)=564 , there are a few exist them and i want to convert to number to text with use a only 1 letter that ")" how can i figure out
    i think if i use matlab it facilitate my works or complicate my works
    sorry for my bad english

  17. Hi,
    I want your help in finding a formula where i can pull of data from multiple row - having multiple data in each cell - and the cell has some key identifier at there first to look out the data.
    For e.g. In row Cell 1- 32:hshsjsjsbsvshh
    Cell2: 32:shsjsjsjsjsjsjjs
    Cell3: 67:hwhsshshshshsh
    Cell4: 69:hshsshsusushsh

    So in above example i want the data in next sheet from the cell which is started from '32:'

    • Hello!
      You did not say exactly how you want to extract the data. Suppose the data needs to be combined. If I understand your task correctly, Merge cells by condition using the array formula

      =CONCAT(IF(LEFT(E2:E10,3)="32:",E2:E10,""))

      Press Ctrl + Shift + Enter so that array function to work.
      We merged all the cells that start with "32:"

      • Hi Alex,

        Thanks for your reply but here i don't want to merged the cell, I want to pull out the data start with 32: as it is in the other excel sheet.
        Hope you understand my query.

        • hello!
          I don't want to guess anymore. Explain what kind of data you wanted to get from Cell 1-32: hshsjsjsbsvshh ??
          Write the expected result for each cell.

  18. I think I've figured it out on my own. Thank you anyway for your help.

  19. Hi,
    I'm trying to write an equation in Excel that rounds using the following rules:
    1. If the extra digit is less than 5, drop the digit.
    2. If the extra digit is greater than 5, drop it and increase the previous digit by one.
    3. If the extra digit is five, then increase the previous digit by one if it is odd; otherwise do not change the previous digit.

    I feel like I'm close to a solution by using the IF(AND(Find line of commands but can't get it to come together properly. Could you please help?
    Thanks so much,
    John

    • Hello John!
      The Find function is applied to the text. In this case, I assume that we have a number. Apply the IF function and rounding function.

      =IF(MOD(INT(A30),2)=0,IF(A30-INT(A30)<=0.5,INT(A30),ROUND(A30,0)),ROUNDUP(A30,0))

      Hope this is what you need.

      • Thank you so much for your response Alexander!
        I tried that function but didn't have much success. Maybe if I give you an example that would help.
        If I have a number like 0.20645 and I would like it rounded to four decimal places and be rounded down if the value in the fourth place is even and the fifth value is =5, in this case 0.20645 would be rounded to 0.2064. On the flip side of that I would still like a value like 0.20615 to be rounded to four places but rounded down if the fourth value is odd and =5. In other words 0.20615 would be rounded to 0.2062.

        This is the equation I've tried without success: =IF(AND(ISODD(MID(H12,FIND(".",H12)+4,4)),MID(H12,FIND(".",H12)+5,4)="5"),ROUNDUP(H12,4),IF(AND(ISEVEN(MID(H12,FIND(".",H12)+4,4)),MID(H12,FIND(".",H12)+5,4)="5"),ROUNDDOWN(H12,4),ROUNDDOWN(H12,4)))

  20. 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!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)