Comments on: How to use Excel RIGHT function - formula examples

Today our focus is on the RIGHT function, which is designed to return a specified number of characters from the rightmost side of a string. Like other Text functions, RIGHT is very simple and straightforward, nevertheless it has a few unobvious uses that might prove helpful in your work. Continue reading →

Comments page 2. Total comments: 94

  1. Hi there!

    thanks a bunch for your help.

    I have this text on A1:
    10.1.4.81'
    I have this text on A2:
    10.1.20.234

    In need to figure out the position of the first "." starting from the RIGHT.
    So I am expecting the results:
    3
    4

    I cannot make it :(

    Much appreciated,
    Josu.

    1. Hello!
      Replace the last "." to "#" using the SUBSTITUTE function.
      The formula below will do the trick for you:

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

      This should solve your task.

  2. I need a formula that can convert text in the format 2/15/2022 2:16:09 PM to date and time dd-mmm-yyyy hh:mm [gives 15-Jan-2022 14:16] and should be able to work correctly even where the date provided is 12/31/2022 2:16:09 PM still in the same format [should give 31-Dec-2022 14:16]

    1. Hello!
      Try this formula:

      =DATE(MID(A2,SEARCH("/",A2,SEARCH("/",A2)+1)+1,4), LEFT(A2, SEARCH("/",A2,1)-1), (MID(A2,SEARCH("/",A2)+1, SEARCH("/",A2,SEARCH("/",A2)+1)- SEARCH("/",A2)-1)))+ MID(A2,SEARCH(" ",A2)+1,20)

      We have a ready-made solution for your task. I'd recommend you to have a look at our Text To Date Tool. It parses over 500 combinations representing dates in text format and converts them to regular Excel dates.

  3. HI,

    do you know of a good way to extract a word or phrase from a cell containing many words? i'm trying to extract "ticker" or "hero" or "hero 2" or "sidekick" using a formula so we know the location of the message. i tried doing a search function but i could only go two deep due to the errors.
    =IFERROR(IF(SEARCH("sidekick",A1)>0,"sidekick","no"),IF(SEARCH("hero",A1)>0,"hero","no"))

    header|click|ticker Take 10% off orders $129+ -- i'm trying to extract just "ticker"
    homepage|click|hero C2002-ECOM @ $99 -- i'm trying to extract just "hero"
    homepage|click|hero 2 Disaster Prep -- i'm trying to extract just "hero 2"
    homepage|click|sidekick The BEST Deals This Month -- i'm trying to extract just "sidekick"

    1. Hello!
      Using the formula, you can extract the first word after the last separator "|" :

      =LEFT(MID(SUBSTITUTE(A1,"|","#", LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))), SEARCH("#",SUBSTITUTE(A1,"|","#",LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))))+1,100), SEARCH(" ",MID(SUBSTITUTE(A1,"|","#",LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))), SEARCH("#",SUBSTITUTE(A1,"|","#",LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))))+1,100))-1)

  4. I have a cell that includes the following (as an example) #14 Wisconsin at #3 Illinois.

    I want to extract the state names into different cells. Is successfully isolated "Wisconsin" into a cell using your page on MID and SEARCH ("=MID(A3, SEARCH(" ",A3)+1, SEARCH(" ",A3, SEARCH(" ", A3)+1)-SEARCH(" ",A3)-1)" .

    I am struggling to isolate "Illinois". I was trying to use the RIGHT LEN AND FIND functions in combination to no avail.

    Any suggestion

    1. Hi!
      To extract the last word from the text, use the formula:

      =RIGHT(SUBSTITUTE(A3," ","@",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))), LEN(A3)-FIND("@",SUBSTITUTE(A3," ","@",LEN(A3) - LEN(SUBSTITUTE(A3," ",""))),1))

      I hope my advice will help you solve your task.

      1. I thought for a moment it worked, but I failed to anticipate something that complicates this.

        In some cases what I am extracting is two words. For example #11 Texas at #42 Oklahoma St resulted in just "St" when I want " Oklahoma St" and for #7 Kansas at #19 W Virginia it resulted in just "W" when I need "W Virgnia"

        This solution results in only the St being extracted from Oklahoma St - is there a way to tell excel that I need everything after the last space before the name.

        Another suggestion is appreciated. It is so close.

        1. Hi!
          I wrote this formula based on the description you provided in your original comment. If you gave the correct description of the problem, time would not be wasted.
          Try the following formula:

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

  5. Hi All,

    I need to remove the first two digits from my excel cells if the length of the characters in greater than 10, i.e. I have cell phone numbers and the I want to remove the country code 91 from the cell phone numbers. I know the RIGHT Formula in excel =RIGHT(A1, LEN(A1)-2). But I need to use it with IF condition. I am trying this but it is not working. Can someone please help: =IF(LEN(A1>10) RIGHT (A1, LEN(A1)-2))

  6. Almost there for me, i just need help please, im trying to get the 6 characters that appear after the second to last backslash.

    eg. in

    \orange rule\apples are nice\pears rock\berrys are yum\strawberry

    i need to get 'berrys' (its always 6 characters and always starts after the second to last backslash)

    1. Hello!
      Please use the following formula:

      =MID(A2,SEARCH("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))-1))+1,6)

      This should solve your task.

  7. Hi Please help me with the below questions answer.

    Input:
    cast(trim(name) as string) as name
    Null as address
    Cast(id as decimal(38,10)) as id

    Output:
    Name
    Address
    Id

  8. Hi Everyone,

    Good day to all! I don't know what to use or how to use the code. But what I need is put a comma after every 9th number.

    Column A
    125306920
    125298912
    125297627
    125300893
    125311521
    125306307

    And if i use CONCATENATE i get this result 125306920125298912125297627125300893125311521125306307. Please help.

    Thanks a lot!

      1. Good day! TEXTJOIN doesn't seem to work in 2013, if it's a plugin/addin i'm not allowed to download it either. :(

  9. Hello guys,

    Can you help me with this. I want to achieve the column B result given the column A as my data.

    In column A: ThePLAN 599 With iPhone 12 128GB Red
    ThePLAN 2799 with iPhone 12 Pro Max 256GB Gold
    ThePLAN PLUS 2999 (Consumable 1299)
    ThePLAN PLUS 599 (Consumable 299)

    Column B shows:

    iPhone 12 128GB Red
    iPhone 12 Pro Max 256GB Gold
    TPP
    TPP

    Thank you,

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IFERROR(RIGHT(A1,LEN(A1)-SEARCH("with",A1,1)-4),"TPP")

      I hope it’ll be helpful.

      1. Woah! it really worked! Thanks a lot!!!!

  10. HOW CAN WE GET NUMBER AFTER THE LAST COMMA IN EXCEL

    question - 1, 2, 5, 25, 223

    answer should be 223

    question - 1,2,5,25, 42

    answer should be 42

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

      =RIGHT(SUBSTITUTE(C1,",","#", LEN(C1)-LEN(SUBSTITUTE(C1,",",""))), LEN(C1)-FIND("#",SUBSTITUTE(C1,",","#", LEN(C1)-LEN(SUBSTITUTE(C1,",",""))),1))

      Hope this is what you need.

  11. I need to substitute the rightmost character so that my column will sort properly:
    if rightmost ="a", make it ".1"
    if rightmost ="b", make it ".2"
    I've tried everything but I don't get the result I need. Please help.

    1. Hello
      Here is the formula that should work perfectly for you:

      =IF(RIGHT(C1,1)="a",REPLACE(C1,LEN(C1),1,".1"), IF(RIGHT(C1,1)="b",REPLACE(C1,LEN(C1),1,".2")))

      Hope this is what you need.

  12. Team, I hope this message finds you and your families all doing well. Need help and can't seem to get Excel to do what I want. I have an excel spreadsheet with IP addresses e.g. 10.9.1.100 I want to subtract the last Octet by 1 and have the output return the revised IP address.
    Example: Column A2 10.9.1.100
    Column A3 (suggested Formula)
    Column A4 output from A3 10.9.1.99
    Thank you for any help you can provide

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =LEFT(A2,SEARCH("#",SUBSTITUTE(A2,".","#",3),1)) & (--MID(A2,SEARCH("#",SUBSTITUTE(A2,".","#",3),1)+1, LEN(A2)-SEARCH("#",SUBSTITUTE(A2,".","#",3),1))-1)

      I hope this will help

  13. Hello!
    I have a text in a cell. Sometimes this text has at the end one empty space (it is visible only if I enter in the cell and go with the cursor to the most right of the text in the cell). I would like to use the right function (or any other function if feasible) in order to take this empty space out of the text in the cell (however the tricky part is that the formula should work only if there is a space in the end of the text, if it is a symbol it should not change the text. Also the formula should not delete other empty spaces within the text, only the empty space at the end of the text). Thanks!

    1. Hello Ivan!
      If I understand your task correctly, the following formula should work for you:

      =IF(RIGHT(D1,1)=" ",REPLACE(D1,LEN(D1),1,""),D1)

      I hope this will help

  14. Please see the following string:
    Cell A1 = Arch Psychological Services 39-9912 106 Street, Edmonton, Alberta T5K 1C5
    I am trying to separate the name of the business and the address as follows:
    Cell B1 = Arch Psychological Services
    Cell C1 = 39-9912 106 Street, Edmonton, Alberta T5K 1C5
    How would you use the RIGHT function? or any other function?
    Thanks

    1. Hello ,
      I want the output this type 101, 1000, 102.
      Please Give me a solution.

      1. ZSW234ER101
      2. ZSW234ER1000
      3. ZSW234ER102

      1. Hello! You can use regular expressions and the user-defined function RegExpExtract to extract numbers from text by pattern. You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex).
        The formula below will do the trick for you:

        =RegExpExtract(A1, "(\d+)(?!.*\d)")

        I recommend paying attention to the Regex tool. You can find, extract, compare, delete, or replace strings that match the regular expression pattern you enter.

  15. Hi if someone could help,
    If I have several domains but I get duplicate but different tld (.com, .net, .org, .fr, etc)
    what formula can I use to get the tld only ?

    1. Hi Loris,

      Is my understanding correct that you want to get only .com, .net, .org, .fr, etc. ? If so, you can use the following formula to extract everything that comes after a dot, including a dot itself:

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

      Where A2 is the cell containing the original domain.

  16. hello,

    I need your help. I want to extract the numbers inside the brackets from the following:

    Access Point 300mb TP-Link WA855RE Range Extender [310575] × 1

    Any ideas? I cant figure out the right formula.

    Thanks

    1. Hello, Olina,
      Thank you for contacting us.

      If we understand your task correctly, the formula below should work for you:
      =IFERROR(LEFT(RIGHT(A1, LEN(A1)- SEARCH("[",A1) - LEN("[") + 1),SEARCH("]", RIGHT(A1, LEN(A1)- SEARCH("[",A1) - LEN("[") + 1))-1), "")

      Alternatively, you can use our Extract Text tool to derive the necessary part of your cell in a few clicks.

  17. hi i please help me get the right formula:

    ex. A1=958.30

    i want to copy only the 2 decimal digits which is 30
    i used the following formula: =RIGHT(A1,2) and the result was .3

    how do i include the "0" ?

    Thank you so much!

    1. FORMAT BOTH COLUMNS AS A NUMBER WITH 2 DECIMAL PLACES

  18. also some records look like this: (comma after state)
    XXXX Via Marina, #JXXX, Marina Del Rey, CA, 90292 USA

    1. Gary:
      Because I can't see any pattern to the structure of your data, it seems you'll have to handle some of the separation process "By Hand". In other words I don't see any option but for you to clean this data up.
      Highlight the cell that contains the data.
      Then from the Data tab select Text-to-Columns.
      Then click the Delimited button then next.
      Then click the Spaces button then next.
      Then General then Finish.
      Now your data is in separate cells. Problem will be the same type of info is not in the same columns.
      You'll have to move the same types into the columns by hand.
      If you need to put the name state and zip into one cell you'll need to concatenate them into one cell.

  19. Hi, I have a form in excel which shows a value in a cell of 806.73 (example in field A1).
    I want to to show each digit in a separate filed i.e 8 goes into A1, 0 into B1, 6 into C1 etc.
    I tried to use the =LEFT(RIGHT(A1,1),1) for the A1 value, =LEFT(RIGHT(B1,2),1) for the B1 value and so on. I need to allow for a monetary value of the following points nnnnnnnnnn.nn
    The problem I have found is that if configure each field (B1, C1, D1 etc) upto a max of 10 and the value shown in A1 is nnn.nn then the values shown in A8 for example is shown as the last point which in this example would be '8' but I need to return a blank
    Any help would be helpful

    1. Hello,

      If I understand your task correctly, please try to do the following:

      1. Supposing that your cell A1 contains a value of 806.73;

      2. Enter the following formula in cell B1:

      =IF(COLUMN(A1)<=LEN($A$1),RIGHT(LEFT($A$1,COLUMN(A1)),1),"")

      3. Just select the cell where you've entered the formula and drag the fill handle (a small square at the lower right-hand corner of the selected cell) to the right.

      Hope this will help.

  20. I am using the following formula to produce a number that is in turn used to produce a list of items 1 - 20 for 131 staff.
    staff list N$17 = 131 (number of staff)
    Staff list $T$1 = 20 (number of items)
    A19 = 131
    D21 = Item 9

    This works to produce a number 1179 that results in item 9 being populated:
    =IF($A19*RIGHT(D21,1)>4000,"Not enough Lines",IF($A19*RIGHT(D21,1)>('Staff List '!$N$17*'Staff List '!$T$1),"",'Scoring Sheet'!$A19*RIGHT(D21,1)))

    However for items 10 - 20 I get the same results as calls 1 - 9 with 0 for 10 and 20...

    E21 = Item 10
    =IF($A19*RIGHT(E21,1)>4000,"Not enough Lines",IF($A19*RIGHT(E21,1)>('Staff List '!$N$17*'Staff List '!$T$1),"",'Scoring Sheet'!$A19*RIGHT(E21,1)))

    Any help would be appreciated. It appears RIGHT is extracting the text but 10 is seen as 0 and 11 as 1 and so on.

    1. No Worries...sorted it :)

  21. How to write a formula to take the value from the right and translate it into another value: Values in A1 - A3 are ddd - 100S, ddd - 100N, ddd - 100, I need to find the S, N and blank and translate that into Sally/Ned/Unassigned

    1. Hi, Donna,

      Try using nested IF together with RIGHT function:
      =IF(RIGHT(H2)="S", "Sally", IF(RIGHT(H2)="N", "Ned", IF(VALUE(RIGHT(H2,1))=0, "Unassigned","")))

      1. Thank you very much, that formula did the trick.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)