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



Graham,

Not what you had in mind, but the attached perl-script should do the
trick. Of course, you will need perl and the Spreadsheet::WriteExcel and
Spreadsheet::ParseExcel modules, and you will need to set it up to read
and write the filenames you want (rather than the ones I put in by
default), and the relevant column.

It's quick and dirty, but I was prompted to have a go, as the basic
problem is text manipulation, which is a perl speciality (and a VB
abomination).

Cheers,

Bruno Prior

Graham Howe 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.
#!/usr/bin/perl

use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel;

# Open existing workbook
my $oldbook = Spreadsheet::ParseExcel::Workbook->Parse('oldbook.xls');

# Create new workbook
my $newbook = Spreadsheet::WriteExcel->new("newbook.xls");

# Set column to be modified
my $adjcol = 0;

# Iterate through sheets on old workbook
foreach my $oldsheet (@{$oldbook->{Worksheet}}) {

# Add a worksheet to new workbook
my $newsheet = $newbook->addworksheet($oldsheet->{Name});

# For each cell in old sheet, assign old value to matching cell in new
workbook
for(my $iR = $oldsheet->{MinRow}; defined $oldsheet->{MaxRow}
&& $iR <= $oldsheet->{MaxRow}; $iR++) {
for(my $iC = $oldsheet->{MinCol}; defined $oldsheet->{MaxCol}
&& $iC <= $oldsheet->{MaxCol}; $iC++) {
# If the cell exists, add it to new workbook
my $oldcell = $oldsheet->{Cells}[$iR][$iC];
if (ref($oldcell)) {
my $oldval = $oldcell->Value;
# If the cell is in the relevant column modify entry
if ($iC == $adjcol) {
$oldval =~ s/(\d+)/sprintf("%03.0d",$1)/ge;
}
$newsheet->write($iR, $iC, $oldval);
}
}
}
}

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.