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:
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:
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.