Lost those leading zeros in Excel / Access?

You’ve imported data into your Excel sheet or Access application and those important leading zeros got lost.  For example, your 5 digit zip code (04231) is now only 4 digits (4231).

Here’s a simple formula to get back the zeros:

Assume your data is in cell B1 and you need the data to have 5 digits.

The formula is = right(“00000” & B1,5)

What this does is pad 5 zeros before the value in B1

 000004231

Then it uses the “right” text function to select the last 5 characters of the string.

So if B1 already has 5 characters, then the formula has no effect.   But if it is missing a leading “0”, then it will be padded in front of the string.

So just match the number of zeros with the number at the end of the “right” function.
Simple!
(In Visual Basic there is a function named Format that is also very useful)

Advertisements
Posted in Access Tips, Excel Tips | Leave a comment

Len Function – Does not work the same in Excel and SQL Server!

A colleague of mine was having a problem with Excel.  He had imported data from a SQL Server database.  When he created a pivot table with the data, one of the data elements displayed twice.  For example, let’s say the field in question was Region (North, East, South, West).  South showed up twice in the Excel pivot table.  

Region  Amount
East 100
North 200
South 300
South 250
West 100

He used the LEN function in excel and saw that some of the data records, “South” was 5 characters, but in others it was 6.  So he went to the source data in SQL server.  There he used the “LEN” function and found that all data showed that “South” was also 5 characters.  He thought he might have found a bug in the import function in Excel.  What was going on?

The answer is that:

  • LEN in Excel gives the length of the string including spaces.
  • LEN in SQL Servers gives the length of the string EXCLUDING trailing spaces.

Some records in the SQL server database had a trailing space (this data came from an outside source so we did not have control of the data quality).

So Excel was importing the data exactly as it existed.  In order to determine the exact length of a string in SQL Server, use the function DATALENGTH.  This counts leading and trailing spaces — but it returns the results in bytes (2 bytes to a character) — so you must divide the length by 2.

Posted in Excel Tips | Leave a comment

Placing the Worksheet Name in a cell in Excel

There are times when you have a large number of worksheets in your Excel Workbook and you want the Worksheet name to also be included in a cell on the Worksheet.  This may be for presentation purposes or just simply to place a visible cue so you are certain you are working on the correct worksheet.

To do this, use the function:
 = cell(“filename”)

This returns the full path of the file followed by the sheet name.  In this case, the file name
is Accounts and the worksheet is Regions.   Note the right bracket just before the name of the worksheet.

Example:
 C:\My Documents\[Accounts.xls]Regions

But there is a catch here!  Using the function as described above returns the worksheet name of
the last cell that was changed.  The retrieved worksheet name will change as you move around your workbook and make changes.  To fix this, you need to use the secondary attribute of the “cell” function and point to a cell on the
current worksheet.  In the example below, I picked cell A1 which will default to the current worksheet.  The formula will look like:

=cell(“filename”,A1)

Now all you need to do is extract the worksheet name from the entire path.  This is where that right bracket comes in.  The formula finds the location of the right bracket and subtracts that from the length of the entire string.  That gives you the length of the worksheet name.  Then you use the “right” function to extract that part of the name.

 =RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1)))

Posted in Excel Tips | Leave a comment

International Software Rollout Considerations

We had a customer considering rolling out their domestic homegrown software to their international locations. Here are some of the things they needed to consider.

The Question

Your business software is working well in the United States at a number of offices.  It is stable.  Your staff understands it.  It meets the business requirements.  It integrates well with other applications. 

You have offices around the world in various cultures.  You believe your business will benefit if all locations, worldwide, can utilize the same software.  This would reduce your IT overhead, improve office to office communications, and streamline operations. 

  • Can the software be rolled out worldwide? 
  • What issues must be considered to ensure that the rollout would be successful?
  • What problems will be encountered?

There are two major topics that these questions raise – how well is the software packaged for rollout and can the software meet international requirements?

Software Packaging
Is the software properly packaged for rollout?  This question would have to be answered, even without the international component.

“Packaged for Rollout” means that:

  • The software can be distributed, installed, configured, user trained and operated with limited headquarters support.  Example:  Intuit’s Quicken software. 
  • If the software is complex, there is local support available for installation, configuration, or training.  Example:  SAP, JD Edwards, Oracle 
  • The software uses industry recognized standards for application integration.  Local offices may have requirements to link the software to other vendors, customers or services.
  • The office has the proper infrastructure to support the application.  As more applications become web enabled, and software moves back toward a centralized model, infrastructure has become less of an issue.  If the applications need to be run locally, then hardware, operating systems and communications infrastructure must be reviewed.  In remote locations, communications infrastructure can still be a critical issue.

Internationalization and Localization. 

What Internationalization  and Localization features and functions must be supported in the software to meet the business requirements of the international office and be accepted by the local user community?

There are many factors that must be considered and are listed below.  However, the culture of the organization as well as the type of business can determine which factors must be addressed. 

For example, if all international offices speak English, and business is conducted in English, then multi-language may not need to be considered as part of the core application.  However, localization may likely be needed to provide reports in local language for external users.

Here are a number of international and localization factors that must be considered.  Depending on the nature of the business as well as the culture of the local office and the region, some of these factors must be addressed and some could be ignored.

Issue

Description

Factors Affecting It

Language

Display the application in the local language.  Address different character sets (ex:  Japanese, Arabic).  Use of Unicode within the application

Does office do business in the local language?  Is staff trained in English?

Writing conventions

Date representation (dd/mm/yy vs. mm/dd/yy).  Time representation (both time display and the time zone).  Calendars.  Number formatting (decimal points, separator character).  Currency symbols, position.

If language is right to left (Arabic, Persian), then the entire user interface may have to be changed.

Is the application used only within the office or is it used by customers or vendors?  Are numbers (financials) a primary focus of the application?

Cultural / Local Content

Names / Titles.  Governmental assigned numbers (ex: the US might use SSN but another country may use another identifier that is in a different format).  Addresses.  Postal Codes.  Sorting.

Images / Colors (appropriateness).  Local customs / content.

These issues may be critical.

Financial

Currency translation – (if business is conducted in multiple currencies).  Tax reporting.  Other governmental reporting.  Weights / measures.

If the software is transaction based, these could be very important.  If it is list based (ex:  mailing lists), this might be less of an issue).

Management

Who will manage software change?  Changing software is disruptive.  People do not like change.

How will the data in the current application be converted to the new application?

If this is a centralized application, what methodology will be used to prioritize and implement new requirements?  How will software improvements be communicated?

Management of an international software application can be far more complex than a domestic application.  Don’t underestimate the politics of software acceptance.  Remember, the remote location can always find a way to ignore headquarter mandates.

Evaluate Your Situation

  • Why rollout your software internationally?
  • What benefits do you expect your organization to get from this rollout?
  • Is your software “packaged for rollout”?
  • Have you identified the international requirements?
  • What are the localization issues?
  • Is your software prepared to handle the international issues and localization issues?
  • What are the internal political issues that will affect software acceptance?
  • What is the benefit the local office will get from this software change?
  • What are the costs of this change?
  • Who will manage future software changes?  How will you ensure that the software keeps pace or ahead of the business requirements?


Posted in IT Strategy/Planning | Leave a comment