Extracting substrings of varying size

[watercooler]

I have inherited an abundance of Excel files that had some sloppy data input. Here is an example:

BBR 689.5 - 690.5 683.0 - 684.0 673.0 - 678.0
LUF1 653.0 - 654.5
LUF2 632.5 - 636.0
MUF1 621.5 - 623.0
MUF2 591.5 - 593.0 589.0 - 590.0

Column A contains the zone name and column B contains the zone intervals. If a zone contains more than 1 zone interval it is separated by at least 4 spaces. I can't figure out how to extract it so there is only 1 zone interval per a line, like so:

BBR 689.5 690.5
BBR 683 684
BBR 673 678
LUF1 653 645.5
LUF2 632.5 636
MUF1 621.5 623
MUF2 591.5 593
MUF2 589 590

Can anyone please help me?

 

[Andrzejek]

Check Split:

Dim str
str = Split("689.5 - 690.5          683.0 - 684.0          673.0 - 678.0", "   ")  '4 spaces

That will give you an array of 3 elements:

str(0) = "689.5 - 690.5"
str(1) = "683.0 - 684.0"
str(2) = "673.0 - 678.0"

HTH

---- Andy

 

[CBasicAsslember]

Instead of VBA have you tried 'Data' --> 'Text to columns'?

 

[CBasicAsslember]

How many sheets total does an "abundance" make?

 

[watercooler]

Awesome - thanks Andrzejek!

How many does an "abundance" make?...about 1800 different Excel files. So needless to say this will save me a ton of time cleaning the data before I build a database for it.

Thanks guys.