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

441 comments

  1. 1. Having input with total four remarks “Received, waived, OTC due, PDD due”
    a. In Column “A" I have number of documents with document description
    b. In Column “B" I have dropdown of Copy or Original
    c. In Column “C" I have Remark of "Received" for Respective document
    d. In Column “E" I have Remark of "Waived" for Respective document
    e. In Column “F" I have Remark of "Short due" for Respective document
    f. In Column “G" I have Remark of " Long due" for Respective document

    2. Outcome required
    a. In Column “A" I required document description either in copy or original having remarks “Received” (1st scenario)
    b. In Column “A" I required document description either in copy or original having remarks “Short due & Long due” (2nd scenario)
    c. In Column “B” I required document nature with outcome of copy or Original as per input data (Required in both scenario)

    Please help here to extract the outcome it will be great help for me thanks in advance

    • Hi!
      I am not sure I fully understand what you mean. You want to put the result in column A, which already has data. Please provide me with an example of the source data and the expected result.

      • Point no 1 Narrate about it's source data through subhead which was inputted in sheet no 1

        & point no 2 narrate about required outcome through source data in sheet no 2 therefor I mention column A & B in both Point because these refer two different sheet. I mention the sample data below for sheet 1. please update excel formula which i can use in sheet 2 of same excel.

        DOCUMENT TYPE Received Waived Short due Long due
        sale deed Copy Received
        RD Copy Waived
        Death certificate Copy Waived
        sale deed 2 Copy Received
        ATS Original Short due
        SPA Original Short due
        sale deed Original Long due
        RD Original Long due
        Death certificate Original Long due

        • Due to some limitation unable to paste excel sheet therefor i mention revised source data for sheet one plz copy & paste the data in excel sheet & share the formula in sheet two
          a. In Column “A" of sheet 1

          DOCUMENT (Heading)
          sale deed
          RD
          Death certificate
          sale deed 2
          ATS
          SPA
          sale deed
          RD
          Death certificate

          b. In Column “B" of sheet 1
          TYPE (Heading)
          Copy
          Copy
          Copy
          Copy
          Original
          Original
          Original
          Original
          Original

          c. In Column “C" of sheet 1
          RECEIVED (Heading)
          Received
          BLANK
          BLANK
          Received
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK

          d. In Column “E" of sheet 1
          WAIVED (Heading)
          BLANK
          Waived
          Waived
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK

          e. In Column “F" of sheet 1
          SHORT DUE (Heading)
          BLANK
          BLANK
          BLANK
          BLANK
          Short due
          Short due
          BLANK
          BLANK
          BLANK

          f. In Column “G" of sheet 1
          LONG DUE (Heading)
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK
          Long due
          Long due
          Long due

          Outcome required in sheet 2

          1. In Column “A" of sheet 2

          a. I required document description either in copy or original having remarks “Received” (1st scenario)
          b. I required document description either in copy or original having remarks “Short due & Long due” (2nd scenario)

          2. In Column “B" of sheet 2

          c. I required document nature with outcome of copy or Original as per input data (Required in both scenario)

  2. hey

    i have a data i need to do the following

    1- i need to select the word ( black )
    2- i need to delet it from the A1 cell
    3- i need to replasce it in cell b1

    ex.

    A1 iphone 6s black
    A2 iphone black 7p
    A3 black flat charger

    what i want to do is

    A1 iphone 6s B1 black
    A2 iphone 7p B2 black
    A3 flat charger B3 Black

    thank you

  3. Excellent content as usual

  4. xboxonefifa14
    3dspokemonmoon
    playstation3ufcundisputed3
    playstation3djhero
    playstation3fifa14
    xbox360pure
    xboxoneforzahorizon2
    playstation2needforspeedunderground
    playstation4yakuza0
    xbox360worms2armageddon
    playstation4soulcaliburvi
    xboxonehitman2

    Hello, I am looking for a way to split the game platforms; (xbox360, xboxone, playstation2, playstation3, 3ds etc) from the game titels; (fifa14, pokemonmoon, djhero etc). Is there a formula I could use ? I tried this formule: =IF(SEARCH("one";A2;2);LEFT(A2;SEARCH("one";A2)+2);1) to split xboxonefifa14 which only worked to split xboxone from the game title, but I couldn't get the game title in a different column. So my question is how I could be able to separate the game titles from different platforms?

      • Hi,

        Thanks for the reply! The formula worked for the most games and I managed to separate the games from the game platform. However, I do run into trouble with the game platforms; xboxone, xbox360, xboxseriesx, pc and 3ds.

        xboxonefifa14 fifa14onefifa14
        3dspokemonmoon pokemonmoon3pokemonmoon
        playstation3ufcundisputed3 ufcundisputed3
        playstation3djhero djhero
        playstation3fifa14 fifa14
        xbox360pure pure360pure
        xboxoneforzahorizon2 forzahorizon2oneforzahorizon2
        playstation2needforspeedunderground needforspeedunderground
        playstation4yakuza0 yakuza0
        xbox360worms2armageddon worms2armageddon360worms2armageddon
        playstation4soulcaliburvi soulcaliburvi
        xboxonehitman2 hitman2onehitman2
        playstation3nhl13 nhl13
        xboxseriesxassassinscreedvalhalla assassinscreedvalhallaseriesxassassinscreedvalhalla
        pctransistor transistor
        pclifeisstrange2episode3wastelands pclifeisstrange2episode3wastelanlifeisstrange2episode3wastelands

        These are a few games I tried to seperate. Do you have any idea what might cause this problem and why it doesn't replace the game platform with a blank value and copies the game + gameplatform instead?

        The formula I used: =CONCAT(IF(ISNUMBER(SEARCH($H$2:$H$21;A2)); SUBSTITUTE(A2;$H$2:$H$21;"");""))

        Input for H:
        xboxone
        xbox360
        playstation3
        playstation4
        playstation2
        playstationvita
        playstation5
        wii
        switch
        3ds
        psp
        ds
        gameboyadvance
        dreamcast
        xboxseriesx
        xbox
        gamecube
        pc
        wiiu
        stadia

        • Hi!
          The names of the gaming platforms have partial matches. If you search for xbox360 it will find xbox360 and xbox. Also "ps" can be found in the name of the game.

  5. FI_AC_BONDS
    FI_AC_OBU_LIQ
    FI_AC_SUKUK
    FI_AC_SUK_OBU
    FI_AFS_BONDS
    FI_AFS_OTH_BUS
    FI_AFS_ST
    FI_AFS_SUKUK
    FI_AFS_SUK_OBU
    UNB_FI_AFS_BOND
    UNB_FI_AFS_SUK

    I want to find if AFS or AC word is available on above table and need to get in displace as AFS or AC in another cell.

    • Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula with SEARCH function:

      =IF(ISNUMBER(SEARCH("AFC",A1)),"AFC",IF(ISNUMBER(SEARCH("AC",A1)),"AC",""))

      If this is not what you wanted, please describe the problem in more detail.

  6. Cell A1 contains Iim Rd, Near Kendriya Vidyalaya, Mubarakpur, Lucknow, Uttar Pradesh 226201

    Cell A2 contains Lucknow

    I need to extract until Lucknow into Cell A3 using a Formula

    Cell A3 FINAL VALUE should be Iim Rd, Near Kendriya Vidyalaya, Mubarakpur ( with or without comma ) is ok

    • Hi!
      Pay attention to the following paragraph of the article above - How to extract text before a specific character.
      It covers your case completely.

  7. Hello!

    thanks for this very interesting article. Can you help me to solve this problem

    [{ 12, name: Adventure}, { 14, name: Fantasy}, { 28, name: Action}]

    i wana making colum B Adventure; colum C Fantasy and Colum C Action

  8. Rin (Re.1) Advanced Detergent Powder, 10g (Pack of 108) (IA) = 108
    Lays (Rs.10) Potato Chips - Cream & Onion, 30g (Pack of 10) (IA) = 10
    Parachute (Re.1) 100% Pure Coconut Hair Oil, 2.3ml (Pack of 36) (IA) =36
    Too Yumm! (Rs.5) Veggie Stix - Chilli Chatka, 14g (Pack of 12) (IA) =12

    Please help here to extract the number after the word " Pack of"

      • No sir,

        Its not working ,
        showing blank when i apply this formula.

          • Hello Sir

            The given formula not working for below cases,

            1.Pampers Baby Dry Pants - M, 2pcs, (Pack of 8)
            2.Colgate (Rs.10) Active Salt Toothpaste, 21g (Pack of 12)

            • Hi!
              The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one. For this data, another formula is needed.

              =IFERROR(MID(A2, SEARCH("Pack of ", A2) + LEN("Pack of "), SEARCH(")",A2,SEARCH("Pack of ", A2)+1)- SEARCH("Pack of ", A2) - LEN("Pack of ")), "")

              • Supper Sir,

                Great Thanks.....

  9. I am trying to extract the month from a string like the following
    CF Customer Oct 07
    Ash Customer Sep 07
    Pete Customer Sep 07
    Can you give me a formula?

  10. Hi,
    Can you please suggest in this, will appreciate your help.

    I have some cells like below

    Sushama.K.CTS -> Sushama.K.CTS -> Arbina.B.CTS
    Snehal.C.CTS -> Pooja.G.CTS
    Sonam.C.CTS -> Sonam.C.CTS -> Sonam.C.CTS
    Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS

    How I can extract the last name from each cells after the ">" symbol.

    Like, my result for above cells should be like

    Arbina.B.CTS
    Pooja.G.CTS
    Sonam.C.CTS
    Sonali.S.CTS

    • I have tried with this formula,
      =RIGHT(Q3424,LEN(Q3424)-SEARCH(">",Q3424))

      but it's giving me this result

      Sushama.K.CTS -> Arbina.B.CTS
      Pooja.G.CTS
      Sonam.C.CTS -> Sonam.C.CTS
      Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS

      but I need the last part

      So please suggest.

      • Hello!
        Replace the last ">" with "#" using the SUBSTITUTE function. Determine the position of this character using the SEARCH function. Starting from this position, extract string from the text using the MID function.

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

        This should solve your task.

  11. Hi, I have a query , it would be great if you could solve this. I have some UPC codes in one cell, i need to copy all the UPC codes into different excel cells.

    077346100626, 011951600003, 781968002106, 692000196342, 885694471981, 715933319937, 199960027704

    I need to copy numbers after every "," sign to different cells.
    Like
    077346100626
    011951600003
    781968002106
    692000196342
    885694471981
    715933319937
    199960027704

    Thanks,

  12. Pozdrav !
    Kako izvuci jednu rec iz jedne celije u kojoj ima vise reci ? Evo nekoliko primera celija iz kojih treba izvuci odredjene reci :
    H1 BRENT_OIL at 02:55:02 SuperTrend up -(jedna celija)
    3_Level_ZZ_Semafor_NRP Alert on CrudeOIL, period M15: SELL SIGNAL Level2 -(jedna celija)
    Apollo Scalper CADCHF M15 BUY @0.74719, TP 0.75019, SL 0.74569 -(jedna celija)
    Problemi : Jedna trazena rec ima vise znacenja : (UP,BULISCH,BUY) Drugi primer : (SELL,BEARISH,DOWN).
    Trazena rec nije uvek na istom mestu u celiji. Kako postaviti formulu koja jednostavno pita prvu celiju da li se u njoj pojavila jdna trazena rec : (UP,BULISCH,BUY) ili (SELL,BEARISH,DOWN) Postavio bih formulu za prvo pitanje u recimo D2 a za drugo E2. Ako se bilo koja rec od trazenih pojavi moze se napisati u istoj celiji gde je postavljena formula ili nekoj drugoj svejedno... Tako bih sa te dve kolone pratio signale koji se pojavljuju u prvoj koloni ? Svasta sam probao pa sam se zamrsio izgleda jednostavno ali je zapetljano...Primeri koje sam koristio pa mi nije bas sve uspelo :
    =IF(SEARCH("UP",D2),G10,H10) =IF(ISNUMBER(SEARCH("UP",D2)),G10,H10)
    =IF(SUMPRODUCT(-ISNUMBER(SEARCH("UP",D2:D2))),"0","UP") =TRIM(MID(D2,SEARCH("UP",D2)+10,LEN(D2)))
    =IF(ISNUMBER(SEARCH("supertrend",D2)),G2,"")&IF(ISNUMBER(SEARCH("apollo scalper",D2)),G3,"")
    Itd... Hvala
    Goran

    Hello!
    How to extract one word from one cell that contains several words? Here are some examples of cells from which certain words are extracted:
    H1 BRENT_OIL at 02:55:02 SuperTrend up - (single cell)
    3_Level_ZZ_Semaphore_NRP CrudeOIL warning, period M15: SALES SIGNAL Level 2 - (single cell)
    Apollo Scalper CADCHF M15 BUY @ 0,74719, TP 0,75019, SL 0,74569 - (single cell)
    Problems: One search word has several meanings: (UP, BULISCH, BUY) Another example: (SELL, BEAR, DOWN).
    The search word is not always in the same place in the cell. How to set up a formula that simply asks the first cell if one of the search words appeared in it: (UP, BULISCH, BUY) or (SELL, BETTER, DOWN) I would set the formula for the first question in say D2 and for the second E2. If any of the words of the requested occurrence can be entered in the same cell where the formula is located or someone else doesn't care ... So, I would follow the signals that appear in the first column from those two columns? I tried everything, so I was confused, it looks simple, but it's complicated ... Examples I used, so I didn't succeed:
    = IF (SEARCH ("UP", D2), G10, H10) = IF (NUMBER (SEARCH ("UP", D2)), G10, H10)
    = IF (SUMPRODUCT (-UM (SEARCH ("UP", D2: D2))), "0", "UP") = TRIM (CENTER (D2, SEARCH ("UP", D2)) + 10, LEN D2)))
    = IF (ISBROJ (SEARCH ("supertrend", D2)), G2, "") & IF (ISBROJ (SEARCH ("apollo scalper", D2)), G3, "")
    Etc ... Thanks
    Goran

    • Hello!
      If I understand the problem correctly, you can use the SEARCH function to search for the desired word. With nested IF functions, you can check two conditions.
      Please try the formula below:

      =IF(SUM(--ISNUMBER(SEARCH({"UP","BULISCH","BUY"},A2))) > 0,$D$2, IF(SUM(--ISNUMBER(SEARCH({"SELL","BEARISH","DOWN"},A2))) > 0,$E$2,""))

      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

      • Pozdrav !
        OK Pomaze Thanks
        Goran

  13. Super helpful. Thanks

  14. Hello Alexander,

    I hope you can help me, I have a list of transactions in column J and a list of text in a separate tab in the same workbook. I want to write a formula to find this text in the separate tab and extract this from the transactions (Column J) and the result should be in Column N.

    For Example:

    Column J

    "Int Debit Order To Settlement
    "Cashsend Digital SettlementCard No.
    Digital Tran Fees Settlement *
    Bal Brought Forward
    Monthly Acc Fee Headoffice *
    Transaction Charge Headoffice *
    "Int Debit Order To Settlement"
    "Acb Debit:External Settlement 19.75 "
    "Digital Transf Cr Settlement"
    "Atm Withdrawal 31.50 TCard No. "
    "Pos Purchase Settlement 4.35 TCard No. (Effective 16/05/2022)"
    "Notific Fee Sms Notifyme 1.20 T2 Sms Notifications"
    PY 4654654654Transfer

    Separate Tab List in Column Q:

    Notific Fee Sms
    Bal Brought Forward
    Pos Purchase
    Acb Debit:External
    Transaction Charge
    Monthly Acc Fee
    Cashsend Digital
    PY 4654654654
    Int Debit Order To
    Digital Tran Fees
    Atm Withdrawal

    In Column N:

    I need the formula to search from the list (Separate Tab) and extract the text from the transactions in Column J.

    If you could assist me with this formula please.

  15. Hi

    Please resolve my below problem:

    I Have data in cells as below:

    A1= 1B90
    A2= 1B113
    A3= 3DE- 61
    A4= 1E-105

    And I want as below:

    B1= B
    B2= B
    B3= DE
    B4= E

  16. Hi

    I need to extract whatever string is between the 3rd and 4th undercover of the texts below:

    asd_KSA_Consideration_LocalFootball_Beverages_Core 18-34_Trueview_Youtube_CLE_February2022_1546960|1862997

    Here the answer should be "LocalFootball"

    asd_KSA_Awareness_LocalFootball_Beverages_Core 18-34_Bumper Ads_Youtube_CLE_February2022_1546960|1440241

    Here the answer should be "LocalFootball"

    asd_KSA_Awareness_LaunchKSA_Beverages_CoreAudience_Bumper Ads_Youtube_CLE_BLS_March2022_1918871|1311320

    Here the answer should be "LaunchKSA"

    asd_KSA_Consideration_LaunchKSA_Beverages_InMarket_Discovery Ads_Youtube_CLE_BLS_February2022_1918871|1740112

    Here the answer should be "LaunchKSA"

    asd_KSA_Awareness_LaunchKSA_Beverages_CoreAudience_Video_DV360_PGD_Adintop_February2022_1918871|1014779

    Here the answer should be "LaunchKSA"

    asd_KSA_Conversion_LaunchKSA_Beverages_CoreAudience_Video_DV360_PGD_DMS_February2022_1918871|1883124

    Here the answer should be "LaunchKSA"

    asd_KSA_Consideration_LaunchKSA_Beverages_CoreAudience_Trueview_Youtube_CLE_BLS_February2022_1918871|1362081

    Here the answer should be "LaunchKSA"

    asd_KSA_Conversion_Launch_Beverages_CoreAudience_Video_DV360_PGD_Vidmatic_March2022

    Here the answer should be "Launch"

    asd_KSA_Conversion_Launch_Beverages_CoreAudience_Video_DV360_PGD_Vidmatic_March2022_Cancelled

    Here the answer should be "Launch"

    asd_JOR_Consideration_Max_Beverages_Core 18-35_Trueview_Youtube_CLE_March2022_OD22|3228

    Here the answer should be "Max"

    asd_JOR_Awareness_Max_Beverages_Core 18-35_Bumper Ads_Youtube_CLE_March2022_OD22|3228

    Here the answer should be "Max"

    asd_KSA_Awareness_CFBQ1_Beverages_CoreAudience_Video_DV360_PGD_MMP_February2022_1963621|1373446

    Here the answer should be "CFBQ1"

    asd_KSA_Awareness_CFBQ1_Beverages_CoreAudience_Video_DV360_PGD_DMS_February2022_1963621|1564216

    Here the answer should be "CFBQ1"

    asd_KSA_Consideration_CFBQ1_Beverages_CoreAudience_Trueview_Youtube_CLE_BLS_February2022_1963621|1508651

    Here the answer should be "CFBQ1"

  17. Hello,

    I have the following statement where I'm trying to pull the numerical value's off the far right side (a: 157.00; b: -0.23; c: 9.00). I have tried the example up the top for RIGHT function but it jut pulls everything excluding the month (Feb). If I drop the LEN function and just use SEARCH it pulls only four characters from the right (a:7.00; b:0.23; c: 9.00).

    Feb 07 Mudgee Vet Surg Mudgee Au 034254325786123489 157.00
    Feb 06 Temple & Webster St Peters Au 25643965992308034556 -0.23
    Feb 05 The Brumby Nepalese R Nadal Au 75123502038260262235719 9.00

    Thank you for your help in advance.

    Cheers,
    Rowie

  18. Dear Sir,
    I have texts in a large xls in the pattern of
    APLHA|Alphanum1|alpha num2|alpha num3 - the length of each alphanum is not fixed. Text has spaces too.

    I need to get all the text upto the last | delimiter - i.e. - "APLHA|Alphanum1|alpha num2"

    Could not make any of the above work. Please assist.

  19. Primary Street Stored in A1
    560, 95, Kanpur Road, Krishna Nagar Village, Krishna Nagar, Alambagh, Lucknow, Uttar Pradesh 226023

    Primary City Lucknow Stored in A2

    a3 should retun text to left of Lucknow ( a2 )

    ie., 560, 95, Kanpur Road, Krishna Nagar Village, Krishna Nagar, Alambagh,

    Thanks in advance

  20. I need to find the string before "-DT" or "-LT" from following

    QDY3-DT-HC00121
    ZYN-LT-000013
    CN-URB-LT-00036A23
    WILT-DT-LPD001

    so the results would looks as follows

    QDY3
    ZYN
    CN-URB
    WILT

    • Hi!
      Pay attention to the following paragraph of the article above: How to extract text before a specific character.
      It covers your case completely.

      =LEFT(A2, IF(ISNUMBER(SEARCH("-DT",A2)-1),SEARCH("-DT",A2)-1,SEARCH("-LT",A2)-1))

      To use two search conditions, use a nested IF function.

      • Thanks for the response, it works perfectly. also I referred the above mentioned paragraph but still couldn't understand logic being used in this formula :( . would you be able to explain your formula. also if I want to add one more string "-SRV", what would be the new formula. hope that would help me to understand the logic

        • Hi!
          To search for multiple variants of a specific character, you can use the formula

          =CONCAT(IFERROR(LEFT(A2,SEARCH({"-SRV","-DT","-LT"},A2)-1),""))

          The IFERROR function will replace failed search errors with empty strings. The CONCAT function converts an array of search results into a single text string.

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