Reading spreadsheet file with varying number of delimiters

[William1225]

I still have this kind of question...

If the number of consecutive space characters is undetermined, how to use this way to get space-split spreadsheet?

[GerdW]

Hi William,

If the number of consecutive space characters is undetermined, how to use this way to get space-split spreadsheet?

See message #2. After applying this function you will have a determined number of spaces…

[William1225]

Hi GerdW,

Thanks to your reply.

I've tried the method from message #2 which use the function of "Search and Replace String".

As the first poster mentioned, it is very slow(almost costs 10 minutes) since the string size is more than 1 million in my text....

But if I use the "Spreadsheet String to Array" function by connecting an array of delimiter, it is fast.

So I wonder if there is a better way to deal with the undetermined number of space delimiter.

[massen]

In the end what I did is to do a repeat cycle of "search and replace all" of "double spaces" with "single spaces" - until there's nothing

to replace - that way I know I only have single spaces left and also know I didn't eliminate any spaces that was needed. This was more robust than the array and fast enough for my files [the search and replace all function is pretty fast, so 1Mb should cause it to take 10min, my text file was 8Mb].

[GerdW · Accepted solution]

Hi William,

~60ms when you don't use RegEx:

[William1225]

Very appreciated to massen and GerdW's help.

It is very nice way to solve my question, I didn't know the "regular expression" is so heavy-computing function to cause the very inefficient result.

I should take care of that in the future.

I'm sorry I can't set your reply as the best solution because I'm not the original poster, all I can do is give the kudos...

Should I create a new thread in the future even the question is the same?