With my workplace, we have part number (item numbers), that, when typed into Excel often get converted into what Excel *thinks* the user means.

For example, Excel makes these changes:

```
00001234   =>   1234
005678.0   =>   5678
1234.560   =>   1234.56

```

Because the spreadsheets come from sources outside our control, we cannot attempt to control the behavior in Excel itself.

I have a utility that's bolted onto Excel, using VSTO (C#), that goes out to a Postgres table and attempts to look up the "Excel part number" and convert it back to the real part number. Simply put, it looks like this:

```
create table mdm.excel_lookup (
    actual_part_number text not null,
    excel_part_number text not null,
    lookup_priority integer not null,
    constraint excel_lookup_pk primary key (actual_part_number)
);

```

To populate this table, I have written a function using plperl that attempt to take any given string and anticipate how Excel will mess it up. I believe I have handled numbers with leading zeros and also trailing zeros that are chopped off after the decimal place.

Unfortunately this doesn't cover everything. I don't think Dates will be possible to anticipate, so I may not even try, unless someone has a great idea. But what about scientific notation? Are there other scenarios I haven't thought of?

Our part catalog has over 1.5 million parts, so there are numerous possibilities of what might happen. If I can capture a fair percentage of them, I'd be happy.

Here is my function thus far. If anyone has ideas on what I can do to capture additional things that Excel might do, I would welcome the feedback. Note this, so far, only handles the scenarios I've listed above.

```
CREATE OR REPLACE FUNCTION excel_part(part_number text)
  RETURNS text AS
$BODY$

  my ($input) = @_;
  if ($input =~ /[A-Za-z]/) {
    return $input;
  } elsif ($input =~ /^0+(\d+)$/) {
    return $1;
  } elsif ($input =~ /^(\d+\.\d*)0+$/) {
    return $1 + 0;
  } else {
    return $input;
  }  

$BODY$
  LANGUAGE plperl VOLATILE
  COST 100;

```

Also, I am not married to plperl. I used it only because I know Perl is really good at text handling.