Find Positions of 1 or More Spaces

mlo356

Board Regular
Joined
Aug 20, 2015
Messages
51
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.

Name Address Age Gender


<tbody>
</tbody>

This is how it should look:

Name
Address
Age
Gender

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

Thanks in Advance!!
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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!
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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