# Find Positions of 1 or More Spaces

#### mlo356

##### Board Regular
I have an excel file with records(rows) that contain(have combined) multiple fields(columns) into one cell and am looking to break the data out into their own columns. I know where to split the record by identifying the start and end positions where there are more than one space between each text. For example, this is one record.

<tbody>
</tbody>

This is how it should look:

<tbody>
</tbody>

I would like to break these 4 pieces of text into their own column.

My questions is, Is there a formula to identify the start and end position where there is more than one space. For example

1. Between Name and Address, there are 10 spaces so I would be looking for a start position of 5, and end position of 10. The formula to produce the 5 and 10 could reside in separate cells on the spreadsheet.
2. Between Address and Age, there are 7 spaces so I would be looking for a start position of 18, and end position of 25. The formula to produce the 5 and 10 could reside in separate cells on the spreadsheet.

 Data Start of 1st Position w/ more than one space End of 1st Position w/ more than one space Start of 2nd Position w/ more than one space Start of 2nd Position w/ more than one space Name Address Age Gender 5 10 18 25

<tbody>
</tbody>

And so on…. Is there a way to do this?

Please let me know if this isn’t clear.

Last edited:

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### LBinGA

##### Board Regular
Have you tried Text to Columns?

LB

#### mlo356

##### Board Regular
Have you tried Text to Columns?

LB

Yes I have tried. It only allows for single spaces unless I am missing something.

#### LockeGarmin

##### Active Member
I'm not sure the results you are asking for are what you really want. Instead of (5, 10, 18, 25) I'm thinking you actually are looking for the results (5, 14, 22, 28) or maybe (5, 15, 22, 29). Can you reconfirm the results you want? If I'm wrong can you help me understand how you got the 10 in your desired results?

Also, can you clarify if you are wanting 1 or more spaces (according to your title) or if you are wanting only more than 1 space (according to your description)?

Thanks!

#### mikerickson

##### MrExcel MVP

A couple of thoughts.

If your data doesn't contain spaces, you could use TextToColumns "treat consecutive delimiters as one" feature.

But if you have data like Bob Smith (many spaces) Bob's Address (many) 44 (many) Male

Then what you could do is make a helper column with the formula =SUBSTITUTE(A1, " ", "~") note that there are two spaces in the second argument.

Then use Text to columns with a ~ delimiter on the helper column.

You may have to TRIM the results as there might be leading or trailing spaces that need removal.

Last edited:

#### mlo356

##### Board Regular
I'm not sure the results you are asking for are what you really want. Instead of (5, 10, 18, 25) I'm thinking you actually are looking for the results (5, 14, 22, 28) or maybe (5, 15, 22, 29). Can you reconfirm the results you want? If I'm wrong can you help me understand how you got the 10 in your desired results?

Also, can you clarify if you are wanting 1 or more spaces (according to your title) or if you are wanting only more than 1 space (according to your description)?

Thanks!

Hi LockeGarmin

My title should reads 2 or more. My mistake.

Thanks!

Also, the results of (5,14, 22, 28) as you suggested would absolutely work and is probably what I should have asked for. I had a formula that returned the position of the first character. If I new where the positions of 2 or more spaces were, I could write a formula to separate the data but having 1 formula to produce (5,14,22,28) would be better.

Sorry for the confusion.

Last edited:

#### mlo356

##### Board Regular
A couple of thoughts.

If your data doesn't contain spaces, you could use TextToColumns "treat consecutive delimiters as one" feature.

But if you have data like Bob Smith (many spaces) Bob's Address (many) 44 (many) Male

Then what you could do is make a helper column with the formula =SUBSTITUTE(A1, " ", "~") note that there are two spaces in the second argument.

Then use Text to columns with a ~ delimiter on the helper column.

You may have to TRIM the results as there might be leading or trailing spaces that need removal.

Hi mikerickson, the substitute formula with helper column worked great. Is there a way to only add a delimiter when there are 2 or more spaces ignoring single spaces? Thanks!

Last edited:

#### mikerickson

##### MrExcel MVP
In B1, put the formula (thanks to Rick for the modification) and drag down.

=SUBSTITUTE(SUBSTITUTE(A1, REPT(" ",2), "~"),"~ ","~")

In C1, put the formula =SUBSITUTE(B1,"~~","~") and drag right and down, until all the double ~~ are removed.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,673
Messages
5,838,706
Members
430,564
Latest member
Raeyven

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

### Which adblocker are you using?

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

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