Re: OT: Excel expertise required

["moogman_98" <paul@xxxxxxx>]

grrrr bloomin web interface... i put a reply in and it didnt post 
it! try again...

Hi Graham,

Not sure if this is exactly what you want... But I have tried it with
just numbers (Excel deleted the dots I put in)

Select your whole spreadsheet, right click it and click  "format
cells" click the  "number" tab and scroll down the left
hand side 
until you get to  "Custom" and click it, then in the box
underneath  "Type:" enter ###,000 and click OK

This should convert everything to 001, 002, 030, 100, etc.

I have also just tried doing it with ###,000. and it also adds the 
dot on the end of each one like so 001., 002., 030., 100., etc.

I think that's what you wanted to do?

If you need help.. Just mail me and I'll see what I can do!

Cheers

Paul


--- In ukha_d@y..., "moogman_98" <paul@c...> wrote:
> --- 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.