Finding Blank Spaces between text

[winskirst]

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">

 

[Jana.Luo]

yes, and if I can somehow find out the double space and replace them by ';' that would do the job as well. But don't know how to do it.

 

[Rick Rothstein]

Hi there, I have got a similar situation here for addresses, but a bit more complicated. They use 2spaces as a diameter. The data looks like this below, in A is the raw data, in B, C, D is the result I wanted, can you please help me out there? Thanks very much!

A B C D

19 YULE PLACE 19 YULE PLACE MASSEY AUCKLAND 0614
MASSEY
AUCKLAND 0614



1 HAMILTON ROAD 1 HAMILTON ROAD WAIMAUKU 0881
WAIMAUKU 0881

Assuming your first value is in cell A1, put this formula in cell B1 and copy it across for as many cells as you think you will ever have split apart data for, then copy all those cells down...

=TRIM(MID(SUBSTITUTE("  "&$A1,"  ",REPT(" ",99)),COLUMNS($B:B)*99,99))

Note: There are two blanks between the first two sets of empty looking quotes and only one space between the last set of quotes.

 

[Jana.Luo]

Thank you Rick. But I need to get the texts in-between 2 space into separate columns, not just get ride of the 2-space delimiter.
referred to the first example given above, I want to put 19 YULE PLACE, MASSEY, AUCKLAND0614 into column B, C , D etc.

 

[Rick Rothstein]

Thank you Rick. But I need to get the texts in-between 2 space into separate columns, not just get ride of the 2-space delimiter.
referred to the first example given above, I want to put 19 YULE PLACE, MASSEY, AUCKLAND0614 into column B, C , D etc.

As long as your spaces are real space and not non-breaking spaces that one tends to get when copying from the web, then the code I posted works fine and does exactly what you asked for. Are you saying you tried my code and it did not work for you? If so, then try this formula instead...

=TRIM(MID(SUBSTITUTE("  "&SUBSTITUTE($A1,CHAR(160)," "),"  ",REPT(" ",99)),COLUMNS($B:B)*99,99))

 

[Jana.Luo]

It works now, thanks Rick!