Re: OT: Excel expertise required

[Bruno Prior <bruno@xxxxxxx>]

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);
}
}
}
}