Finding largest instance of consecutive prefix spaces in a worksheet

[lmoseley7]

I'm copying a PeopleSoft department tree into Excel. For those unfamiliar, it is a grouping of departments similar to a file structure where certain departments roll up into other nodes (folders) that all eventually roll up to the ultimate parent node (folder). When pasting this into Excel, the levels I see in PeopleSoft are now denoted by preceeding spaces to indicate the former roll up structure. In the past I have found the largest number of preceeding spaces and starting with that number, used find and replace to replace the spaces with commas and then used text to columns to create the separation between levels through columns. I'd like to automate this with a macro.

What I envision is searching for the largest consecutive number of spaces and then working from that number down to 2, find and replace each with commas. Alternatively, I guess we could start with a number, say 10 for instance, and test for 10 spaces, then 9, etc. until we find the number and then use that to work through the find and replace code. I'm sure I could write something very clunky that would ultimately work, but I was hoping someone here might be able to offer a suggestion of how to make this happen in an efficient manner, both from a code and a coding perspective.

Thanks in advance for the help.

 

[lmoseley7]

Thanks again for your help and explanation. I will study this code and try to find other ways I can use it as well.