Re: OT: Excel expertise required

["moogman_98" <paul@xxxxxxx>]

--- In ukha_d@y..., "Graham Howe" <graham@s...> wrote:
> 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.