Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Thread: Finding Blank Spaces between text

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

    Exclamation Finding Blank Spaces between text

    I need to find data in two different formats within a column

    Examples

    Webb Christopher


    Greer Nancy

    I need to find cells with text that have more than one space between the first and last names; and cells with text that have only one space between the first and last names.

    I am fairly new to excel. I can find the space after the first word but cannot tell it to find ones that say have a space then another string of text.

    Thank you for your help!

  2. #2
    Board Regular JamesW's Avatar
    Join Date
    Oct 2009
    Location
    Basingstoke, England
    Posts
    1,197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding Blank Spaces between text

    What are you using to find the ones with 1 space?
    HTH, James

    Light travels faster than sound. This is why some people appear bright before you hear them speak.

    Time is an illusion. Lunchtime doubly so.


  3. #3
    New Member
    Join Date
    Nov 2010
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding Blank Spaces between text

    (LEFT(A3,SEARCH(" ",A3,1)

  4. #4
    New Member
    Join Date
    Nov 2010
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding Blank Spaces between text

    Correction: LEFT(A3,SEARCH(" ",A3,1)

    Need to find the next string. And thought I would put it into an if then statement to differentiate between the two different formats.

    Thanks.

  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,700
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding Blank Spaces between text

    What is your end objective here. If it is to separate the two names into two cells, then here is an approach.

    Sheet1

     ABCD
    10Webb Christopher Webb Christopher
    11Greer  Nancy Greer Nancy

    Spreadsheet Formulas
    CellFormula
    C10=LEFT(A10,SEARCH(" ",A10,1))
    D10=TRIM(RIGHT(A10,LEN(A10)-LEN(C10)))
    C11=LEFT(A11,SEARCH(" ",A11,1))
    D11=TRIM(RIGHT(A11,LEN(A11)-LEN(C11)))


    Excel tables to the web >> Excel Jeanie HTML 4
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.

  6. #6
    New Member
    Join Date
    Nov 2010
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding Blank Spaces between text

    The objective is to separate the different formats. We are not going to separate the names. I did take my first step from the formula. I don't think it is quite on tract for what I need to do, but I cannot think of any way to find a space of more than one space. The number of spaces is the only difference in the formats.

    Thank you.

  7. #7
    Board Regular Dannyh1's Avatar
    Join Date
    Nov 2009
    Location
    Liverpool UK
    Posts
    1,138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding Blank Spaces between text

    Try,

    =IF(LEN(SUBSTITUTE(A1," ",1))<>LEN(A1),"Contains 2 spaces","")

    There are 2 spaces between " ".

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

    Default Re: Finding Blank Spaces between text

    Thanks! I think that is closer. But I tried it and both formats come up with " contains two spaces.

  9. #9
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding Blank Spaces between text

    If they'll only contain 1 or 2 spaces:
    Code:
    =IF(TRIM(A1)<>A1,"2+ spaces","1 space")
    This will return 2 spaces even if there's more than 2 though (TRIM removes all extra spaces from a text string).
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  10. #10
    Board Regular Dannyh1's Avatar
    Join Date
    Nov 2009
    Location
    Liverpool UK
    Posts
    1,138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding Blank Spaces between text

    Will TRIM remove spaces between words though? I thougt it was just from the end?

    The solution I posted seems to work for me - have they definatley only got on space?

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
  •