Need to isolate the 4th segment in string

[Gil149]

I have a strings of data that often look whats below, I can isolate the first segment and the last segment...but I can't get the middle segments. The number of spaces can vary and it's turning out to be a nightmare. Forums/Google haven't yielded results (I may be looking for the wrong thing). Tried delimiters and parsing but nothing is working. I tried delimiting to a single space and treating consecutive spaces as a single...but sometimes there is a word with a space and that is causing a problem as its technically the same column in the source...is there a way to delimit only if there are two spaces and treat 2+ spaces as the delimiter? The number I need to grab is the "2212337". I put the string in using the CODE since it kept trimming spaces off the line.

151555         7018719             191199             2212337        11/02/2017  EROS      FDE

 

[Joe4]

Are you sure that your data isn't fixed width delimited?
If so, all the fields may line up (i.e. first field starts at column 1, second field starts at column 15, etc)?
If so, you can use Text to Columns to delimit, using the Fixed Width option and placing the lines at the correct space (should be evident, as all the fields will line up when viewed).

 

[steve the fish]

Could those numbers in the first 3 'columns' contain more than one number?

 

[Gil149]

Joe4,
Not fixed with or tab delimited...tried both options before posting...It's a pull from a very old database we need data from and it's the grand 'ol clean up process.

 

[Gil149]

Could those numbers in the first 3 'columns' contain more than one number?

it's possible that there can be a single space, with both items needing to be in the same column...anytime I have a double space or greater is when I need the delimiting to happen...

 

[Joe4]

How about replacing two spaces with something like a pipe symbol ("|"), and then use Text to Columns, delimiting on the pipe symbol, and treating consecutive delimiters as soon?
That should solve the problem, as the single spaces will not be converted to a pipe symbol, so your data will not be split on those.

 

[steve the fish]

This is very convoluted but seems to work:

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",REPT("|",LEN(A1))),"| ","|")," ","@"),"|"," "))," ",REPT(" ",LEN(A1))),3*LEN(A1),LEN(A1)))

 

[Gil149]

Joe4,
That works...should have thought about that. I was trying to solve double spaces to a single...it's a Monday morning for sure. Thank you both for the help!

 

[Joe4]

That works...should have thought about that. I was trying to solve double spaces to a single...it's a Monday morning for sure. Thank you both for the help!

No problem! Sometimes it helps to toss another set of eyes and brain on the problem!
Glad I could help.