Need to build a path based on text indent level

Hi,
I have a category system that is created by a web site. There are 4 levels to my categories and my system spits it all out in a single column
eg
Musical Instruments
~~Strings
~~~~Pianos
~~~~~~Grand
~~~~~~Concert
~~Woodwind
~~~~Flute
~~~~Oboe

I need these expressed as a path.

e.g
\Musical instruments\Strings\Pianos\Grand\
\Musical instruments\Strings\Pianos\Concert
\Musical Instruments\Woodwind\Flute\
\Musical Instruments\Woodwind\Oboe\

The indents always have a specific number of spaces in front of them so I can easily add a helper column with level number.

I've attached a spreadsheet with an example.

Any help would be greatly appreciated.

Thanks

Note: It looks like the forum has stripped the spaces from in front of the text so I have replaced them with a tilde

Please update your forum profile with your current version of Excel - I suspect you have updated from Excel 2007. Thanks.

It might be worth pointing out that the OP will need to replace semi-colons in the formula with commas for the Australian locale:

=IF((FIND(LEFT(TRIM(A2)),A2)+1)/2=1,"\"&A2&"\",LOOKUP(2,1/((FIND(LEFT(TRIM(A$2:A2)),A$2:A2)+1)/2=(FIND(LEFT(TRIM(A2)),A2)+1)/2),C$2:C2)&TRIM(A2)&"\")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.