Thanks:  0
Likes:  0

# Thread: Finding Blank Spaces between text

1. ## 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. ## Re: Finding Blank Spaces between text

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

3. ## Re: Finding Blank Spaces between text

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

4. ## 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. ## 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

 A B C D 10 Webb Christopher Webb Christopher 11 Greer  Nancy Greer Nancy

 Cell Formula 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

6. ## 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. ## Re: Finding Blank Spaces between text

Try,

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

There are 2 spaces between " ".

8. ## 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. ## 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).

10. ## 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?

## User Tag List

#### Posting Permissions

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