Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Find character counting from the right

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find character counting from the right

    I know I've seen this done but for the life of me can't figure it out.

    I want to find the first space in a text string (in a cell) counting from the right.

    ie.... in Cell A1 I have "now is the time"

    I want a formula to tell me that the first space from the right is at position 5.

    Thanks!

  2. #2
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,722
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find character counting from the right

    Probably a better way to do this, but this works...
    =LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  3. #3
    New Member
    Join Date
    Feb 2013
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find character counting from the right

    Is this it:

    Code:
    =(LEN(A1)-FIND(" ",A1))+1

  4. #4
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,722
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find character counting from the right

    Quote Originally Posted by uzaname View Post
    Is this it:

    Code:
    =(LEN(A1)-FIND(" ",A1))+1
    That gives 12?

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  5. #5
    New Member
    Join Date
    Feb 2013
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find character counting from the right

    I thinkit only works if there is only one space. Here's a VBA function:

    Code:
    Function TEST(r As Range) As Long
        For i = Len(r.Value) To 1 Step -1
            If Mid(r.Value, i, 1) = " " Then
                TEST = i
                Exit Function
            End If
        Next i
    End Function

  6. #6
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,722
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find character counting from the right

    Quote Originally Posted by uzaname View Post
    I thinkit only works if there is only one space. Here's a VBA function:
    the formula I suggested, while a bit cumbersome, returns 5 in the provided sample, and works on other samples I created...
    A
    B
    1
    now is the time
    5
    2
    now is the too
    4
    3
    now one t
    2
    4
    now two two aabbccdd
    9

    B1=LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1
    copied down

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  7. #7
    New Member
    Join Date
    Feb 2013
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find character counting from the right

    Yes, sorry, I meant my first one was wrong.

  8. #8
    New Member
    Join Date
    Nov 2015
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find character counting from the right

    Quote Originally Posted by FDibbins View Post
    the formula I suggested, while a bit cumbersome, returns 5 in the provided sample, and works on other samples I created...
    A
    B
    1
    now is the time
    5
    2
    now is the too
    4
    3
    now one t
    2
    4
    now two two aabbccdd
    9

    B1=LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1
    copied down

    This does it!

    Thanks so much.

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Find character counting from the right

    Quote Originally Posted by uzaname View Post
    I thinkit only works if there is only one space. Here's a VBA function:

    Code:
    Function TEST(r As Range) As Long
        For i = Len(r.Value) To 1 Step -1
            If Mid(r.Value, i, 1) = " " Then
                TEST = i
                Exit Function
            End If
        Next i
    End Function
    There is a much simpler VBA solution than that available...
    Code:
    Function Test(S As String) As Long
      Test = InStrRev(S, " ")
    End Function
    However, these functions do not give the answer the OP wanted. The OP wanted the count from the right side of the text, our functions above give the count from the left side. Although the OP has a formula solution, for completeness sake, here is the UDF version...
    Code:
    Function LastSpace(S As String) As Long
      If S Like "* *" Then LastSpace = Len(S) - InStrRev(S, " ") + 1
    End Function
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,259
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Find character counting from the right

    Perhaps

    Code:
    Function LastSpaceFromRight(aString As String) As Long
        LastSpaceFromRight = 1 + Len(Split(StrReverse(aString), " ")(0))
    End Function
    Last edited by mikerickson; May 1st, 2017 at 02:52 AM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •