OT: Excel expertise required

["Graham Howe" <graham@xxxxxxx>]

A very off topic post here, but I thought there might be an Excel 
expert here.

I have a column of cells with strings like this:

1.
1.1.
1.1.1.
1.2.
1.2.1.
1.2.2.
1.3
2.

etc. there are 55,000 rows. I need to convert the data to be in the 
following format:

001.
001.001.
001.001.001.
001.002.
001.002.001.
001.002.002.
001.003
002.

There can be anything up to 9 'levels' in the structured code and 
anything up to about 150 'entries' within a level. So for example the 
following would be valid:

1.2.3.4.5.6.7.8.9.
1.20.3.110.1.

and would need to be converted to:

001.002.003.004.005.006.007.008.009.
001.020.003.110.001.

I did manage to write a very complicated set of formulae that 
achieved the desired effect, but it meant adding formulae in another 
10 columns (with 55,000 rows) and constantly slows to halt and 
sometimes crashes due to memory problems.

I can't help thinking that there must be a 'neat' solution that is 
far less resource hungry. but I just can't see it. I know that I 
could do something with macros (vba) that might help, but I would 
prefer a formula based approach if possible.

Regards

Graham

Yahoo! Groups Sponsor

ADVERTISEMENT

http://www.automatedhome.co.uk
Post message: ukha_d@xxxxxxx 
Subscribe:  ukha_d-subscribe@xxxxxxx 
Unsubscribe:  ukha_d-unsubscribe@xxxxxxx 
List owner:  ukha_d-owner@xxxxxxx
List of UKHA Groups here - http://groups.yahoo.com/group/UKHA_Grouplists/

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.