various runs of spaces as delimiters

**Excelvator** · 08-24-2012, 06:12 PM

How can I identify and number beginning of consecutive spaces in a string -- so I can parse the string?

**shg** · 08-24-2012, 06:18 PM

You could use TRIM and there would be any.

**Excelvator** · 08-24-2012, 06:22 PM

shg, My problem is to remove each set of spaces in separate executions so that I can parse out the text between the spaces. Thanks!

**shg** · 08-24-2012, 06:36 PM

I don't know what that means .... How about an example.

**Excelvator** · 08-24-2012, 06:41 PM

08/18/11 2359 Discharge Outpatient HTSP IMAGING SERVICES MIC

---------- Post added at 04:41 PM ---------- Previous post was at 04:40 PM ----------

08/18/11 2359 Discharge Outpatient HTSP IMAGING SERVICES MIC
When I post this reply it trims the spaces between items which number from 5 to 10.

**shg** · 08-24-2012, 06:43 PM

That's what you have -- what do you want to do with it?

**Excelvator** · 08-24-2012, 06:45 PM

08/18/11 2359
08/18/11 2359

Discharge

Outpatient

HTSP IMAGING SERVICES MIC

Then put into row.

**shg** · 08-24-2012, 06:47 PM

And the number of spaces between each field is always >1, and constant from one record to the next?

What are the spacings?

**Excelvator** · 08-24-2012, 06:51 PM

Always >1, and never constant.

**shg** · 08-24-2012, 07:33 PM

If you can use a UDF,

Please Login or Register  to view this content.

E.g.,

Please Login or Register  to view this content.

Select B2:E2, paste =ParseX(A2) in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.

If you don't want a UDF, someone will likely paste a formula-based solution.

If the varying spaces because the fields always start at fixed positions withing the string, a formula solution would be easy.

**MrShorty** · 08-24-2012, 09:53 PM

If I understand what you are doing (and I'm not sure I do), you could use the TExt to columns command -> select delimited data -> select space as the delimiter and make sure consecutive delimiters as on is checked.