Finding Blank Spaces between text

winskirst

New Member
Joined
Nov 1, 2010
Messages
16
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!
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
What are you using to find the ones with 1 space?
 

winskirst

New Member
Joined
Nov 1, 2010
Messages
16
(LEFT(A3,SEARCH(" ",A3,1)
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 

winskirst

New Member
Joined
Nov 1, 2010
Messages
16
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.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
7,139
Office Version
  1. 365
Platform
  1. Windows
What is your end objective here. If it is to separate the two names into two cells, then here is an approach.

Excel Workbook
ABCD
10Webb ChristopherWebbChristopher
11Greer NancyGreerNancy
Sheet1
 

winskirst

New Member
Joined
Nov 1, 2010
Messages
16
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.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
Try,

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

There are 2 spaces between " ".
 

winskirst

New Member
Joined
Nov 1, 2010
Messages
16
Thanks! :) I think that is closer. But I tried it and both formats come up with " contains two spaces.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
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).
 

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
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?
 

Forum statistics

Threads
1,175,652
Messages
5,898,679
Members
434,722
Latest member
Jude_Beh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top