The UK Home Automation Archive

Archive Home
Group Home
Search Archive


Advanced Search

The UKHA-ARCHIVE IS CEASING OPERATIONS 31 DEC 2024


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: OT: Excel expertise required


  • To: ukha_d@xxxxxxx
  • Subject: Re: OT: Excel expertise required
  • From: "moogman_98" <paul@xxxxxxx>
  • Date: Wed, 18 Sep 2002 20:47:42 -0000
  • Mailing-list: list ukha_d@xxxxxxx; contact ukha_d-owner@xxxxxxx
  • Reply-to: ukha_d@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.

Home | Main Index | Thread Index

Comments to the Webmaster are always welcomed, please use this contact form . Note that as this site is a mailing list archive, the Webmaster has no control over the contents of the messages. Comments about message content should be directed to the relevant mailing list.