Excel substring: how to extract text from cell

The tutorial shows how to use the Substring functions in Excel to extract text from a cell, get a substring before or after a specified character, find cells containing part of a string, and more.

Before we start discussing different techniques to manipulate substrings in Excel, let's just take a moment to define the term so that we can begin on the same page. So, what is a substring? Simply, it's part of a text entry. For example, if you type something like "AA-111" in a cell, you'd call it an alphanumeric string, and any part of the string, say "AA", would be a substring.

Although there is no such thing as Substring function in Excel, there exist three Text functions (LEFT, RIGHT, and MID) to extract a substring of a given length. Also, there are FIND and SEARCH functions to get a substring before or after a specific character. And, there are a handful of other functions to perform more complex operations such as extracting numbers from a string, replacing one substring with another, looking up partial text match, etc. Below you will find formula examples to do all this and a lot more.

How to extract substring of a certain length

Microsoft Excel provides three different functions to extract text of a specified length from a cell. Depending on where you want to start extraction, use one of these formulas:

  • LEFT function - to extract a substring from the left.
  • RIGHT function - to extract text from the right.
  • MID function - to extract a substring from the middle of a text string, starting at the point you specify.

As is the case with other formulas, Excel substring functions are best to learn from an example, so let's look at a few ones.

Extract substring from start of string (LEFT)

To extract text from the left of a string, you use the Excel LEFT function:

LEFT(text, [num_chars])

Where text is the address of the cell containing the source string, and num_chars is the number of characters you want to extract.

For example, to get the first 4 characters from the beginning of a text string, use this formula:

=LEFT(A2,4)
LEFT formula to extract a substring from the start of a string

Get substring from end of string (RIGHT)

To get a substring from the right part of a text string, go with the Excel RIGHT function:

RIGHT(text, [num_chars])

For instance, to get the last 4 characters from the end of a string, use this formula:

=RIGHT(A2,4)
RIGHT formula to extract a substring from the end of a string

Extract text from middle of string (MID)

If you are looking to extract a substring starting in the middle of a string, at the position you specify, then MID is the function you can rely on.

Compared to the other two Text functions, MID has a slightly different syntax:

MID(text, start_num, num_chars)

Aside from text (the original text string) and num_chars (the number of characters to extract), you also indicate start_num (the starting point).

In our sample data set, to get three characters from the middle of a string beginning with the 6th character, you use the following formula:

=MID(A2,6,3)
MID formula to extract text from the middle of a string

Tip. The output of the Right, Left and Mid formulas is always text, even when you are extracting a number from a text string. If you want to operate on the result as a number, then wrap your formula in the VALUE function like this:

=VALUE(MID(A2,6,3))

Extract substring before or after a given character

As shown in the above examples, the Left, Right and Mid functions cope nicely with uniform strings. When you are dealing with text strings of variable length, more complex manipulations shall be needed.

Note. In all of the below examples, we will be using the case-insensitive SEARCH function to get the position of a character. If you want a case-sensitive formula, use the FIND function instead.

How to extract text before a specific character

To get a substring preceding a given character, two things are to be done: first, you determine the position of the character of interest, and then you pull all characters before it. More precisely, you use the SEARCH function to find the position of the character, and subtract 1 from the result, because you don't want to include the character itself in the output. And then, you send the returned number directly to the num_chars argument of the LEFT function:

LEFT(cell, SEARCH("char", cell)-1)

For example, to extract a substring before the hyphen character (-) from cell A2, use this formula:

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

No matter how many characters your Excel string contains, the formula only extracts text before the first hyphen:
Extracting text before a specific character

How to extract text after character

To get text following a specific character, you use a slightly different approach: get the position of the character with either SEARCH or FIND, subtract that number from the total string length returned by the LEN function, and extract that many characters from the end of the string.

RIGHT(cell,LEN(cell)-SEARCH("char", cell))

In our example, we'd use the following formula to extract a substring after the first hyphen:

=RIGHT(A2,LEN(A2)-SEARCH("-",A2))
Extracting text after a specific character

How to extract text between two instances of a character

To get a substring between two occurrences of a certain character, use the following generic formula:

MID(cell, SEARCH("char", cell)+1, SEARCH ("char", cell, SEARCH ("char", cell)+1) - SEARCH ("char", cell)-1)

The first two arguments of this MID formula are crystal clear:

Text is the cell containing the original text string.

Start_num (starting point) - a simple SEARCH formula returns the position of the desired character, to which you add 1 because you want to start extraction with the next character.

Num_chars (number of chars to extract) is the trickiest part:

  • First, you work out the position of the second occurrence of the character by nesting one Search function within another.
  • After that, you subtract the position of the 1st occurrence from the position of the 2nd occurrence, and subtract 1 from the result since you don't want to include the delimiter character in the resulting substring.

For example, to extract text surrounded by two hyphens, you'd use this formula:

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

The screenshot below shows the result:
Extracting text between two hyphens

If you are looking to extract text between 2nd and 3rd or 3nd and 4th occurrences of the same character, you can use a more compact SEARCH SUBSTITUTE combination to get the character's position, as explained in How to find Nth occurrence of a character in a string:

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

In our case, we could extract a substring between the 2nd and 3rd hyphens with the following formula:

=MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))-1)
Extracting a substring between the 2nd and 3rd hyphens

How to find substring in Excel

In situations when you don't want to extract a substring and only want to find cells containing it, you use the SEARCH or FIND function as shown in the above examples, but perform the search within the ISNUMBER function. If a cell contains the substring, the Search function returns the position of the first character, and as long as ISNUMBER gets any number, it returns TRUE. If the substring is not found, the search results in an error, forcing ISNUMBER to return FALSE.

ISNUMBER(SEARCH("substring", cell))

Supposing, you have a list of British postcodes in column A and you want to find those that contain the substring "1ZZ". To have it done, use this formula:

=ISNUMBER(SEARCH("1zz", A2))

The results will look something similar to this:
Finding cells that contain a specific substring

If you'd like to return your own message instead of the logical values of TRUE and FALSE, nest the above formula into the IF function:

=IF(ISNUMBER(SEARCH("1zz", A2)), "Yes", "")

If a cell contains the substring, the formula returns "Yes", an empty string ("") otherwise:
Formula to identify cells that contain a certain substring

As you may remember, the Excel SEARCH function is case-insensitive, so you use it when the character case does not matter. To get your formula to distinguish the uppercase and lowercase characters, opt for the case-sensitive FIND function.

For more information on how to find text and numbers in Excel, please see If cell contains formula examples.

How to extract text from cell with Ultimate Suite for Excel

As you have just seen, Microsoft Excel provides an array of different functions to work with text strings. In case you are unsure which function is best suited for your needs, commit the job to our Ultimate Suite for Excel. With these tools in your Excel's arsenal, you just go to Ablebits Data tab > Text group, and click Extract:
Extract Text tool in Excel

Now, you select the source cells, and whatever complex strings they contain, a substring extraction boils down to these two simple actions:

  1. Specify how many characters you want to get from the start, end or middle of the string; or choose to extract all text before or after a given character.
  2. Click Insert Results. Done!

For example, to pull the domain names from the list of email addresses, you select the All after text radio button and type @ in the box next to it. To extract the user names, you select the All before text radio button, as shown in the screenshot below.
Define the substring you want to extract.

And you will get the following results in a moment:
Parts of email addresses are extracted in separate columns.

Apart from speed and simplicity, the Extract Text tool has extra value - it will help you learn Excel formulas in general and substring functions in particular. How? By selecting the Insert as formula checkbox at the bottom of the pane, you ensure that the results are output as formulas, not values.

In this example, if you select cells B2 and C2, you will see the following formulas, respectively:

  • To extract username:

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

  • To extract domain:

    =IFERROR(RIGHT(A2, LEN(A2)- SEARCH("@",A2) - LEN("@") + 1),"")

How much time would it take you to figure out these formulas on your own? ;)

Since the results are formulas, the extracted substrings will update automatically as soon as any changes are made to the original strings. When new entries are added to your data set, you can copy the formulas to other cells as usual, without having to run the Extract Text tool anew.

If you are curious to try this as well as many other useful features included with Ultimate Suite for Excel, you are welcome to download evaluation version.

More formulas for substrings in Excel

In this tutorial, we have demonstrated some classic Excel formulas to extract text from string. As you understand, there can be almost infinite variations of these basic scenarios. Below you will find a few more formula examples where the Text functions come in handy.

Available downloads

Excel substring functions - practice workbook (.xlsx file)
Ultimate Suite - trial version (.exe file)

Table of contents

425 comments

  1. I have list of below countries and wanted only (HA) and first word from sentence eg. (HA) Cameroon, (HA) United States.
    (HA) Cameroon
    (HA) Brazil E2B
    (HA) Brazil LLP
    (HA) Cameroon ViiV
    (HA) United States E2B

    Is there an easy way to do this with a formula?

  2. Hi,

    I'm struggling with a formula for a set of lengthy descriptions with a lot of words and numbers. Each description has a reference within it which all start the same, and have either 5, 10, or 15 numbers.
    Example:
    ABC-12345
    ABC-12345-12345
    ABC-12345-12345-12345

    I'm currently using =LEFT(AF5,FIND(" ",AF5)-1) which works for the ones where the reference is at the left hand side, but sometimes the reference is in the middle of the description.

    Can you help at all?

      • Hi Alexander,

        Thanks for your reply.

        Below is an example of how we currently would have the data displayed. The description in column B ,and we are extracting the ABC reference from the description and putting it into it's own reference column A (If we say Reference is Column A, and Description is column B).

        Column A Column B
        Reference Description
        ABC-11464-11321 ABC-11464-11321 - Description of service
        ABC-67923-08131 Customer Name / ABC-67923-08131 / Description of Service / Dates of service
        ABC-18828-11342-11291 Customer Name Invoice Number ABC-18828-11342-11291 - Month - Service Name
        ABC-71296 Ledger Name/H/Coding correction/ Date /ABC-71296 - Contract Title. Email Address

        We are currently using the left formula from the first comment if the description is suitable, otherwise we are manually typing it in and there can be thousands of lines to do this to.

        The descriptions can come in various text orders but will always have a ABC- reference, but then that ABC reference can either have 5,10 or 15 numbers in the formats I have listed.

        So I was just wondering if there is anyway we can extract the ABC reference - regardless of it's length, and regardless of the order of the wording in the description.

        Thanks,
        Hayley

        • Hi! I don't know why you didn't use the link to the article I gave you. It covers your case completely.
          If I understand your task correctly, try the following formula:

          =TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",99)), MAX(1,FIND("ABC", SUBSTITUTE(B1," ",REPT(" ",99)))-50),99))

          • Hi.

            Thank you for your help. I have just adjusted this to make it work in the particular file I needed it for, and it works perfectly.

            Appreciate your help.

  3. Hi! I would love some help!

    I want to extract text from a cell that identifies the 'team' that is being called out. For example, the output I am looking for would be [Team A;Team B; Team C] from the cell example below:

    Team A: XYZ Description
    Team B: XYZ Description
    Team C: XYZ Description

    I am using the =LEFT(A2, SEARCH(":",A2)-1) example, but I only receive [Team A] as the output instead of the [Team A;Team B; Team C] output that I am trying to extract.

    Appreciate the help!

    • Hi! Based on your data, the formula might look something like this:

      =LEFT(SUBSTITUTE(SUBSTITUTE(A1,"XYZ Description",""),":",";"),LEN(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"XYZ Description",""),":",";")))-1)

      The SUBSTITUTE function replaces unnecessary data with a blank and the ":" character with ";"

  4. Hi sir, i am having A2 below 3 Line , how i will export INC-170 , IAM-860 and INC-180 to another Cell

    INC-170: To Classify and Label all Information not handled by Information Systems

    INC-180: To Classify and Label all Information not handled by Information Systems

    IAM-860: Access Review and Renewals

    • Hi! If I understood the question correctly, you want to extract the text before the ":" character. Pay attention to the following paragraph of the article above: How to extract text before a specific character. It covers your case completely. For example:

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

  5. I need the following to be split into 2 or 3 columns if possible, with the text before the "-" in column 1, then the date Monday, January 1, 2024 in the 2nd column, and the bible reference in column 3. The entire row currently contains a reference link, and would like to keep that if possible.

    Day 1 - Monday, January 1, 2024 Luke 5:27-39; Genesis 1:1-2:25; Psalm 1

    e.g. of split:
    Column 1 - "Day 1"
    Column 2 - "Monday, January 1, 2024"
    Column 3 - "Luke 5:27-39; Genesis 1:1-2:25; Psalm 1"

  6. Hi There.. I've numerous text stings in below two formats.
    I need to get my quantity details i.e. "24011 & 13174" from below text string. What formula would you suggest to get the quantities in both scenarios ?
    Thank you so much in advance.

    PPT => 29-DEC-2023 00:00:00 => 24011 => LT

    SON | GGS | 06-JAN-2024 | Fuel 13174 Liters

  7. Hi
    I have a list of property addresses and post code all in one cell, post codes are separated with a delimiter, I want to extract the post codes into a separate column, I have seen some of the examples in the article above but some of the post codes are different char values i.e. CO2 8TY, E4 6TR

    Can I use the RIGHT(SEARCH) function to achieve this?

    Thanks

    • Hi! To advise you on the formula, give an example text string with address and post code and specify what text you want to extract from it.

      • Hi,

        Examples:
        45 Marwin Close, Martock,TA126HL
        Flats Fro703, Flat 703 Frobisher House, London, Gb,SW1V 3LW
        38 Morton Street,CA25UU
        72 Fitznael Street,W12 0BB

        I want the post codes from the right of the delimiter, i.e. TA126HL

        The examples above you can see some of the post codes are different char length

        • Hi! If I understand your task correctly, the following formula should work for you:

          =MID(A1, SEARCH("#", SUBSTITUTE(A1,",","#", LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))+1, 20)

          Count the number of "," in the text using the LEN function by removing the "," from the text using the SUBSTITUTE function. Read more: How to count specific characters in a cell.Then replace the last character "," with "#" and determine its position using the SEARCH function. Starting from this position, extract all characters from the text using the MID function.

          • That worked perfectly, thank you

  8. Hello, I am summarizing all the 10k+ mailer daemons received from my Outlook and need to get only the email addresses (which for sure will be of any number of characters) from this messages:

    Your message to 10131967@ YAHOO.COM couldn't be delivered.
    Your message to lindavid.me@ gmail.com couldn't be delivered.
    Your message to bulanhagui_roseann@ yahoo.com couldn't be delivered.

    can anyone help please? Thank you.

  9. What formula would break the below list into separate columns like the following example:
    A2: R&W 1.5 oz A3: shampoo A4: 200 A5: per case A6: $77.50
    B2: R&W 2.7 oz B3: conditioner B4: 200 B5: per case B6: $74.53

    R&W 1.5 oz shampoo 200 per case $77.50
    R&W 2.7 oz conditioner 200 per case $74.53
    R&W 1.5 oz lotion 200 per case $74.55
    R&W 1.5 oz body wash 200 per case $74.00
    R&W 2 oz shampoo 100 per case $48.00
    R&W 2 oz conditioner 100 per case $45.01
    R&W 2 oz lotion 100 per case $65.00

  10. This was so comprehensive and well illustrated! Thank you.

  11. Precisely what I was looking for, thank you!

  12. Exelent

  13. hi ...
    I need to extract the check sign from the name and count the sign after the extraction
    would you please help me with that .....

    1. R tafis ✅✅✅✅✅✅✅✅
    2. Anton ✅✅✅✅✅✅✅✅✅✅
    3. Iqbal ✅✅✅✅✅✅✅✅
    4. Kosong
    5. Irvan ✅✅✅✅✅✅✅✅✅
    6. Rommy ✅✅✅✅✅✅✅✅✅✅✅✅
    7. Eva Nudin ✅✅✅✅✅✅✅
    8. Yun Haryadi ✅✅✅✅✅✅✅✅✅
    9. Guna ✅✅✅✅✅✅✅✅✅
    10. Gun S ✅✅✅✅✅✅✅✅✅
    11. Gun S ✅✅✅✅✅✅✅✅✅
    12. Muklis ✅✅✅✅✅✅✅✅✅✅
    13. M.Nasir ✅✅✅✅✅✅✅✅✅
    14 Fahmi ✅✅✅✅✅✅✅✅

      • hi ....
        thanks for your kind reply ....
        i'm not intend to remove that character, i need that character to be separated in one column ...
        how should do that so that i'm gonna have a column which filled with the characters alone ....
        thank for your kin attention

        regard

        • Please re-read the article above, it covers your case completely. Use the SEARCH function to determine the position of the desired character. Use the MID function to extract characters from the text starting from this position.

          =MID(A1,SEARCH("✅",A1),20)

          • great .... i found it .....
            thanks a lot ....
            really helpful

  14. I want to extract one word from the following.
    Can you help me with this

    "Å KODA KUSHAQ 1.0L TSI 85KW AT AMBITION BSVI-PH2"
    I want to extract only "KUSHAQ" from the above sentence in excel. Please suggest formulae

  15. Hi all, need help with this one please. I have this in a cell "Treasury rate: 4.85%Tenure: 12 month(s)", and i need to extract these in to separate cells in the same row:

    Cell1: 4.85%
    Cell2: 12
    Cell3: month(s)

    Appreciate some help

  16. I have this in a column and I need to extract the word in front of ISS: Like "Anifowose ISS" and "Sanya ISS". What will be the line of formular pls?

    1. Refill of 33kV Bus Section SF6 gas at Anifowose ISS
    2. Maintenance of 33kV yellow phase transformer breaker at Sanya ISS that was reported glowing.

    Thanks

      • Hi, and thank you for all the answers you've posted!!
        Can you also use the TEXTBEFORE AND TEXTAFTER functions in an IF formula?

  17. Please help.
    BMW - Active E 094
    how to extract Active E from the text?

  18. Hi there,

    im trying to create a string that would provide me with the right info here that i can use for a vlookup from another table.

    I have some that I need the data before the dash-, some without the dash at all where i need one or two characters and some where i need the first character after the dash as well. so for instance:

    8Q-123 - I need 8Q-
    N12645 - I Need N
    VP-C234 - i Need VP-C

    is this possible?

    thanks

    • Hi! There is no common pattern or regularity in your desired results. Use a separate formula for each of your examples. All the information you need is in the article above. For example,

      =LEFT(A1,3)

      • Unfortunately that wont work as the data is changed frequently.

        thanks anyway!

        • I have string come out from the machine that I need to extract. Format as following : 20hour23minute34.56 or 1hour5minute14.16, how can I substract minute value from the text.

  19. Hi,

    I need a help if anybody can help. I just need to extract the letter between the numbers.

    Data in column A:
    362T200
    1000RT200
    1000MST200

    What I need to get in column B:
    T
    RT
    MST

    Thank you for any help!

  20. Hola, necesitaría vuestra ayuda si es posible...

    De la siguiente cadena "ALFARO ALMEIDA, ALFREDO", me gustaría extraer a otra celda todo lo que está antes de la coma, de forma que pueda separar los apellidos del nombre, ya que los recibo de esa forma.

    He probado la siguiente fórmula que he visto en tutoriales, pero me da error: =IZQUIERDA(F2,BUSCAR(",",F2)-1)

    Gracias de antemano, saludos.

  21. How to extract a word from Cell A to Cell B based on the list/range given in Cell C.
    For Example-
    Given - Cell A - I like Dogs
    I need the word "Like" in Cell B
    Based on Cell C range - Like , Love , live etc.
    What would be the formula?

      • Hi!
        I passed 1day searching to do the same thing but with wildcards and possible multiple matches across a same string.

        "sample sentences" "Expected_Results" "Lookedup_Patterns"
        wrong spell match : A.01 sample A.01 A.?
        single match A2.22 sample in sentence A2.22 A?.
        double match test B1.44 and B2.55 sample B1.44, B2.55 B?.

        I tested many combinations but none works the way I need ! This is the closest I achieved :
        {=TEXTJOIN(",";TRUE;IFNA(INDEX(TEXTSPLIT($M2;" ");MATCH($S$2:$S$4&"*";TEXTSPLIT($M2;" ");0));""))}

        what look strange is that the return of the MATCH() is a list of 3 items and thus don't list more than one similar pattern match per sentence

        Thanks in advance for your expertized advice ;)

        • sorry, the tabs were not preserved :
          Column M - Column Q - Column S
          "sample sentences" - "Expected_Results" - "Lookedup_Patterns"
          wrong spell match : A.01 sample - A.01 - A.?
          single match A2.22 sample in sentence - A2.22 - A?.
          double match test B1.44 and B2.55 sample - B1.44, B2.55 - B?.

          • Forgot to say that I got it to work using Alex's VBA RegExpExtract code and the "\s[A-za-z0-9]+\.[A-za-z0-9]+\s" pattern...
            but would have loved to find a non-VBA dependent formula.

        • Sorry, it's not quite clear what you are trying to achieve. To ensure clear understanding of your task, can you provide an example of the source data and the desired result you are aiming for?

          • Ok sorry for that,

            Column M : include 3 rows with sample sentences :
            row2 = sentence 1 = "wrong spell match : A.01 sample"
            row3 = sentence 2 = "single match : A2.22N sample in sentence"
            row4 = sentence 3 = "double match : with B1.44 and B2.55 sample"

            Expected results are
            row2 = A.01
            row3 = A2.22N
            row4 = B1.44, B2.55

            Column S was my list of patterns to be tested by the MATCH() command

            is it more clear ?

            any other patterns like "-22.55" shall not be returned

  22. HI! i need a super big help. I'd love to have your knowledge and expertise to find a solution to this:

    i have two columns with information (text) that is not coming in the same order EX:

    Column A: Finance Service Account; HR Business Partner; Client Account
    Column B: Client Account; Finance Service Account

    I am ok with the ones which are repeated, i want a formula to find the differences between the two columns and throw the HR Business Partner as a result. your help would be very much appreciated. thank you!

  23. please help with my requirement , i need to extract all string of 10 characters from a column ,

    the format of the string is XX12345678 ( first two characters are alphabets and next 8 will be numbers)

    is it possible ? can someone help with the formula.

      • Thank for the care and response.Apologies if i was not clear.

        The position of the string is not reliable. the query is to extract all 10 letter string from a cell.

        the position can be anywhere,

        for EG:

        please process below components

        xxxxxxxxxx
        yyyyyyyyyy
        aaaaaaaaa

        above is the information in a cell, which may vary and does not have a standard format,

        but the components (xxxxxxxx or yyyyyyyyyy or aaaaaaaaaa)will be 10 letter string.

        here when i run the formula i need to extract all the 10 letter components alone.

  24. [{"wid"=>"XI62KKQ", "product_title"=>"PHILIPS HD6975/00(882697500010 Grey 8710103895244 Grey 8995", "quantity"=>1, "fsn"=>"OTNFGZ6VGX4AHYRH", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B7-G0-006", "product_type"=>"otg_new", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}, {"wid"=>"XIA5758", "product_title"=>"Pigeon, Gas Stove Combo - Brunet 3 Burner Gas Cooktop + Flat tawa 250 + Fry Pan 240, 14776, Black, 8904216517760, Stainless Steel, Manual, 3, Brass Burner, 2 Year Warranty from Manufacturer 5494", "quantity"=>1, "fsn"=>"GSTG4ACRZKMZTMH2", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B9-G0-012", "product_type"=>"gas_stove", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}, {"wid"=>"XIB7OIF", "product_title"=>"Butterfly Rapid Plus Black 8906134070730 3399", "quantity"=>1, "fsn"=>"ICTG8MY8VZKZWYDY", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B9-G0-012", "product_type"=>"induction_cook_top", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}, {"wid"=>"VN82165", "product_title"=>"EUREKA FORBES Trendy Zip Red & Black 8901561211282 Red, Black 3799", "quantity"=>1, "fsn"=>"VCLE8YYKSCFXJHE6", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-C1-G0-015", "product_type"=>"vacuum_cleaner", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["S5578085164"], "serial_numbers"=>[]}]}, {"wid"=>"XI5WROX", "product_title"=>"Inalsa QuickVac Red, Black 8903019008123 Red, Black 4195", "quantity"=>1, "fsn"=>"VCLFJ227GEGB2TR2", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B9-G0-017", "product_type"=>"vacuum_cleaner", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}, {"wid"=>"XI7NUV7", "product_title"=>"HAVELLS Instanio White & Mustard 8901762420209 12260", "quantity"=>1, "fsn"=>"WGYFT4CAEQH3QZPD", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B7-G0-006", "product_type"=>"water_geyser", "relabel"=>false, "item_details"=>[{"order_item_id"=>"ANRCA03E5672A7B4B69B9CB9DFE9B66D507", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}]

    In this string I need to retain value "wid"=>"#####", "wid"=>"#####" as many times it occurs.

    Please help me with the formula so that I can extract values .

  25. Hello Alexander Trifuntov (Ablebits Team)

    Please help here to get excel formula to calculate OT (overtime hours count),

    Example as follows :

    In time -9:00:00 AM
    Out time-7:20:00 PM
    Working Hours-10:20
    OT hours - ?

    Below is the output details but we need formula for the same,

    Working Hours Completed OT (Overtime)
    9:00 hours 0
    9:44 Hours 0
    9:45 Hours 1
    10:44 Hours 1
    10:45 Hours 2
    11:44 Hours 2
    11:45 Hours 3

    Criteria to apply
    1.Minimum working hours should be 9 hours
    2.for first OT(Overtime) tobe count for 45 min's after that it takes 1 hours

    • Hi!
      I’m sorry but your description doesn’t give me a complete understanding of your task. Correct me if I’m wrong, but I believe the formula below will help:

      =INT(D1/TIME(0,45,0))

      D1 - working hours

      • Sir

        Minimum working hours should be 9 hours
        for first OT(Overtime) tobe count for 45 min's after that it takes 1 hours

        In time -9:00:00 AM
        Out time-7:20:00 PM
        Working Hours-10:20
        OT hours - ?

        We need to calculate OT (Overtime),
        below is the example for OT Calculation -Manual working

        Ex1- If 9:00 hours completed, OT Will be 0
        Ex2- If 9:44 Hours completed, OT Will be 0
        Ex3- If 9:45 Hours completed, OT Will be 1
        Ex4- If 10:44 Hours completed, OT Will be 1
        Ex5- If 10:45 Hours completed, OT Will be 2

  26. Hello,

    thank you for a great article!

    I have a situation as follows: a textstring in cell A1, which was the caption of a post on social media. This caption contains (besides text) multiple links/URL. In cell B1, I would like to have a list of those links/URL only.

    This is a repetitive task, in which I have a list of post text in column A. Each cell shall consist different number of links/URL.

    Which formular a could use to obtain such a list in column B? Many thanks.

    Regards
    ED

  27. Hi!

    I have a cell with multiplication formula. for example c2 is "=3,2*2,6". I want to take those 3,2 to d2, and 2,6 to e2.

    Sorry for my english!

    • Hi!
      If your formula is written as text, use substring functions to extract numbers from text.

      =--LEFT(C2,SEARCH("~*",C2)-1)
      =--MID(C2,SEARCH("~*",C2)+1,10)

      If a formula is written in cell C2, use FORMULATEXT to get formula text.
      =--LEFT(FORMULATEXT(C2),SEARCH("~*",FORMULATEXT(C2))-1)

      I hope my advice will help you solve your task.

  28. Hello!

    I am processing product information for an online shop and I need to make a meta description for Google. There is a limit of 160 characters.

    From the beginning of the product description, I should take no more than 160 characters. In order for the sentence not to appear incomplete, is it possible to make a logic or a formula with which we can take text ending with the symbol " ." /end of the sentence/, but the total length is not more than 160 characters. It can be less than 160 but not more.

    For an example of the text below, I need to take up to 160 characters, but finish to the end of a sentence:

    The Discovery Artisan 128 digital microscope has a built-in 3.5-inch LCD display, onto which the image is transferred from the objective. It can also be displayed on an external screen, not only on a PC but also on a TV. The microscope is compatible with Windows and Mac OS devices, and you can connect the microscope to them using a USB cable.

    160 characters arе:
    The Discovery Artisan 128 digital microscope has a built-in 3.5-inch LCD display, onto which the image is transferred from the objective. It can also be display

    But I want to get the characters till the end of the first sentence:
    The Discovery Artisan 128 digital microscope has a built-in 3.5-inch LCD display, onto which the image is transferred from the objective.

    or till the end of the second sentence:
    The Discovery Artisan 128 digital microscope has a built-in 3.5-inch LCD display, onto which the image is transferred from the objective. It can also be displayed on an external screen, not only on a PC but also on a TV.

    Please note that every product description is different and I`m looking for an automated formula or rule to take the information of many products into one Excel file.

    Thanks in advance!

    • Hello!
      Use the MID function to extract 160 characters from the text. Use the XMATCH function to find the position of the last dot in these characters. Extract this number of characters.

      =LEFT(A2,XMATCH(".",MID(A2,ROW(A1:A161),1),0,-1))

      I hope it’ll be helpful.

  29. Need to find the count of Agents under TL2 with a score >80

    TL Score
    TL1_Agent1 98%
    TL2_Agent1 52%
    TL2_Agent2 88%
    TL3_Agent1 40%
    TL3_Agent2 77%
    TL3_Agent3 101%
    TL3_Agent4 68%
    TL1_Agent2 45%
    TL2_Agent3 81%
    TL2_Agent4 23%

  30. I have a cell string that looks like this
    A-1 Mfg. Co., Inc.
    I want to extract the 1st 6 characters into another cell excluding hyphens, spaces and commas so my output looks like this
    A1MfgC
    How do I do the formula?

  31. Hello.

    Does anyone have a formula for extracting the "A1466" in this example?

    MacBook Air Core i5 A1466 13 1.8GHz 8GB 128GB 2017

    The number of characters after the "A" will always be either 4 or 5 characters long however, there will sometimes be other words in the string that start with "A". In this case "Air". The "A" number is also not always in the same position in the cell.

    In fact, rather than extracting the number what is actually ultimately required is to clean the string so that it will result in eg (without the "A" number)

    MacBook Air Core i5 13 1.8GHz 8GB 128GB 2017

    Is this achievable with any kind of search/ substitute type function?

    Thanks in advance.

  32. I am trying to extract parts of a date in a cell to format it differently on a different sheet.

    Starting with a cell containing "2023-03-11T14:07:58", "I need to reformat this as 2023-03-11 14:07:58 UTC"

    Can I do this by combining some of the functions on this page? Or is there another way?

  33. I want to extract the flat and Tower in this example is denoted by TL which comes right after the cheques from the left. Is there anyways to get it right?

    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007747~902620102 CHQ. NO:007747 23062026972504242645 - AE0190095
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007746~902620102 CHQ. NO:007746 23062026972504242643 - AE0189708
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007724~902620102 CHQ. NO:007724 23062026972504242638 - AE0189619
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007723~902620102 CHQ. NO:007723 23062026972504242626 - AE0189194
    CLEARING CHEQUESF1203 TL19 DEFAULT - CHQ. NO: 000131~302620177 23062026023062001250 - AE0173816
    CLEARING CHEQUESF408 TL7 DEFAULT - CHQ. NO: 000097~102620128 23062026023062001249 - AE0173462
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007722~902620102 CHQ. NO:007722 23062026972504242630 - AE0141381
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007720~902620102 CHQ. NO:007720 23062026972504242633 - AE0141371
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007726~902620102 CHQ. NO:007726 23062026972504242605 - AE0141335
    OUTWARD CLEARINGF603 TL5 DEFAULT - ~2549518001 CHQ. NO: 000017~804020101 23061026023061001594 - AE0060948
    CLEARING CHEQUEINWARD CLEARING CHQ. NO: 007717~902620102 CHQ. NO:007717 23061100000012570428 - AE0012593

  34. I have a list of TEXT, over 90,000 lines . I copied from a webpage, approx 25,000 names and added it to my list. Now when I sort the list alphabetically, it does not work properly. For example the following is an extract
    ABLAZING GRACE
    ABLE BEAUTY
    ABLE HIT
    ABLE IVY
    ABLE LASS
    ABLE LOTTY
    ABLE MAGPIE
    ABLE MILLIE
    ABLE QUEST
    ABLE RAMON
    ABLE SABLE
    ABLE TO RUN
    ABLE VIVA
    ABLE BONNIE
    ABLE CUSTOMER
    ABLE FAME
    ABLE LANE
    ABLEBE

    You will see that ABLE BONNIE, ABLE CUSTOMER, ABLE FAME and ABLE LANE are not in the correct order. These are what I added from a webpage. Interestingly, if I retype the name and resort my list, the retyped value is sorted correctly.

    I cannot possibly spend the hours to retype all these names - does anyone have a solution - I have tried everything I know ! Thank you

  35. Hi,

    I am trying to extract the Last Name - "Twain" from this data:

    Shania Twain\ShanTw00

    Is there an easy way to do this with a formula?

    • Hi!
      Please read the above article carefully. Try to use information in this article: How to extract text before a specific character and How to extract text after character.

      =RIGHT(LEFT(A2, SEARCH("\",A2)-1), LEN(LEFT(A2, SEARCH("\",A2)-1))-SEARCH(" ", LEFT(A2, SEARCH("\",A2)-1)))

  36. Hi! I have the following data set and I need to extract weather the model is "RHS" or "LHS" using only =SEARCH

    RHS FRONT SEAT ASY 2WAY COMP
    FRONT SEAT ASY COMP RHS 2WAY
    FRONT SEAT ASY COMP LHS 4WAY
    RHS FRONT SEAT ASY COMP 6WAY

    Many thanks!

    • Hi!
      I don't quite understand what you want to extract. Give me an example of the result you want to get. But in any case, you need to use other Excel functions besides the SEARCH function. Please read the above article carefully.

  37. 23.13 1/17/2023 P64634 01 DOE, JOHN
    123.14 1/27/2023 6463401 DOE, JASON ALLEN
    3.14- 12/15/2023 F 6463401 DOE, JASON ALLEN

    Could use some help if anyone can. Needing a macro or formula that will put this one cell of data usually in Column A into five different columns. Each line of data is a dollar (sometimes a negative after the value), date, account number (sometimes the is a letter that is separated in front or there is a digit or two separated at the end), and then a name. Always Last, First (sometimes the middle name or initial is there). Text to column won't work since there are not always the same width in between the different data types and sometimes the account number has the space in the beginning and/or the end. Any help would be appreciated. Thanks! :)

    • Hi!
      Your text strings do not have a single pattern or a single unique delimiter with which to separate text by columns. I'm really sorry, we cannot help you with this.

  38. Hi,
    I wondering if you could help me out with this.

    I have a column with alphabets "A","B","U" and blanks . "A" means Main part ; "B" means Sub Part; "U" means Miscellaneous.
    I want the descriptions of the alphabets in the next column. Is there a way?

    Many thanks!

  39. Hi ! Thank you for all the information.

    My case is a bit tricky.

    I want to extract the address of a string of many different characters.
    Eg - From:

    “ [ { code: “ABC” } ; { value : “123” } ; { name : “ABC” } ; { code: “XYZ” } ; { value : “&$)
    ” } ; { Address : “5 Conrad Street 2710” } ; { code dit : “JDI<-” } ; { value : “123” } ; { name : “$;&;7:?” } ;] “

    I want to only have the address - 5 Conrad Street 2710.

    Note that the number of characters of the address will vary. But the characters right before and after will be the same
    Before : Address : “
    After : ” } ; { code dit :

    Is there a way to do that ? Thank you !

    • Hello!
      Use the advice from the article above. Extract text after certain characters. Then, in the resulting text string, extract the text before certain characters.
      Try this formula:

      =LEFT(RIGHT(A2,LEN(A2)-SEARCH("Address : ",A2)-10), SEARCH("} ; { code dit :",RIGHT(A2,LEN(A2)-SEARCH("Address : ",A2)-10))-3)

  40. I have a cell A2 that has ABC COMPANY #332; DEF COMPANY #254. I need to extract the 4 character numbers #332 and #254. I would like multiple columns to pull in each instance. I can successfully get the 1st instance using the formula

    =TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("#",A2),LEN(A2))," ",REPT(" ",4)),4)) will result in #332

    How can i pull in the 2nd instance #254 in another column?

  41. Please help on how to extract or split if the start of the characters are the same?
    Example I want to extract only the words after the second "20L" which is ERASE on below.
    9D21461T5580739.11PHMC1020LP4ETR20LERASE

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

      =MID(SUBSTITUTE(A2,"20L","#",2), SEARCH("#",SUBSTITUTE(A2,"20L","#",2))+1,50)

  42. What about if the two characters are different? Which character do you put where?
    For example I am looking to to pull out just "240" from the string "200- COGS : 240 - Prof Services Mgmt" in a different cell. In this example, I'm looking for the section of the string between the "-" character and ":" character.

    I'm using the formula =MID(F2, SEARCH(":",F2) + 1, SEARCH("-",F2,SEARCH("-",F2)+1) - SEARCH(":",F2) - 1), but I think I need to change around the "-" and ":", but not sure where. I am always needing to break out these 4 pieces of information from a string like this and it takes forever. So if someone could tell me the best formula to use to get each of the pieces of information (200, COGS, 240, Prof Services Mgmt), that would be really helpful!
    Thanks!

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 :)