Business Automation and Software Blog

Excel Tips and Tricks: Text Lengths in Excel

Posted by Robert Baran on Fri, Jun 22, 2012 @ 01:56 PM

Excel Tips and Tricks / Business Reporting Question: I need to export the stock list to our database program for further analysis. Last time I tried the routine, the names of the products were truncated because the wrong field size was used in the table. How can I return the number of characters in a text string? This will enable me to identify the maximum number as the field size when designing the table in the database.

Answer: By using the Len and Max functions.

Why: To identify the maximum number after counting the length of the text

Applies To: Excel (2010, 2007, 2003, XP, 2000, 97):

1. Assuming that you want to count the text length for the products given below:

Excel Tips

2. Select cell D4 and type =Len(C4).

3. Press the Enter key and copy the formula down.

4. Select cell D22 and type =Max(D4:D21).

5. The screen shot below will be displayed:

Excel Tips

One is able to specify the correct field size (31) for the product names when designing the table in the database. Thus there will be no incomplete data through truncations when the stock list is exported to the database program.

Contact PositiveVision ► ► for a Complimentary  Reporting Consultation

Topics: Business Reporting, Excel Tips and Tricks