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 a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
(LEFT(A3,SEARCH(" ",A3,1)
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 
Upvote 0
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">
 
Upvote 0
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
 
Upvote 0
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">
 
Upvote 0
Try,

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

There are 2 spaces between " ".
 
Upvote 0
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">
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top