+ Reply to Thread
Results 1 to 11 of 11

Function to count text spaces in a cell.

  1. #1
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Function to count text spaces in a cell.

    Hello I am using the "LEFT" function to extract a piece of text from a cell that I then use as the lookup value in a Vlookup formula. Is there a function able count the text spaces in a cell so that i can use that info in conjunction with an IF formula to determine how many spaces I use the "left" formula?

    this is the formula I am using now:

    =VLOOKUP(LEFT(A8,5),'Violation Information'!$A$3:$C$114,3)

    What I want i am trying to accomplish is below:

    =if(A8=11 text spaces,VLOOKUP(LEFT(A8,5),'Violation Information'!$A$3:$C$114,3),VLOOKUP(LEFT(A8,6),'Violation Information'!$A$3:$C$114,3)

    Thanks!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Function to count text spaces in a cell.

    The number of spaces in a cell is just =LEN(A1) - LEN(SUBSTITUTE(A1, " ", ""))

    You sure that's what you want?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Function to count text spaces in a cell.

    You could do that like this

    =VLOOKUP(LEFT(A8,IF(LEN(A8)=11,5,6)),'Violation Information'!$A$3:$C$114,3)

    Note: if you are looking for an exact match then your VLOOKUP should have a final argument of FALSE.....,i.e.

    =VLOOKUP(LEFT(A8,IF(LEN(A8)=11,5,6)),'Violation Information'!$A$3:$C$114,3,FALSE)

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Function to count text spaces in a cell.

    same answer as shg!
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Re: Function to count text spaces in a cell.

    Let me clarify, if cell A8 has 15 characters, i want to use this vlookup:

    =VLOOKUP(LEFT(A8,5),'Violation Information'!$A$3:$C$114,3,false)

    if cell A8 has 16 characters, I want to use this vlookup:

    =VLOOKUP(LEFT(A8,6),'Violation Information'!$A$3:$C$114,3,false)

    the difference between the two is the amount of characters I pull in the "LEFT" function in the Vlookup.

    If you have a better way to do it, please feel free to share.

    Thanks!

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Function to count text spaces in a cell.

    Then I think DLL has you covered.

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Function to count text spaces in a cell.

    So it can just be

    =VLOOKUP(LEFT(A8,LEN(A8)-10),'Violation Information'!$A$3:$C$114,3,false)

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Function to count text spaces in a cell.

    got examples of the text you want so split?

  9. #9
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Re: Function to count text spaces in a cell.

    Daddylonglegs, thanks for the help, but the last formula isnt working. i am getting an NA Error.. I am looking at my lookupdata as the source.

    martindwilson I need to run the vlookup on the red text.

    Count of Q6.9.3
    Count of Q6.10.3

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Function to count text spaces in a cell.

    The text you show in red looks to be the last 6 characters out of 15 or the last 7 out of 16, LEFT function gives the the text from the start not the end......

    Try

    =VLOOKUP(REPLACE(A8,1,9,""),'Violation Information'!$A$3:$C$114,3,false)

  11. #11
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Re: Function to count text spaces in a cell.

    Hooray for Dyslexia!!! i did not want to use the left formula, I wanted to use the Right formula.

    Thanks for everyones help!

    clayton

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1