LEN Formula in ms excel
MS Excel

This excel function can help you in Master data cleansing

• • 4 Comments

This excel function can help you in Master Data cleansing

In SAP Implementation project we provide master data template in MS Excel to core team members. they prepare the relevant SAP master data in those MS excel sheets and finally that data comes to SAP Consultant. Master Data cleansing is duty of core team members, but many times core team member requires help from SAP Consultant in data cleansing activities. One of the important criteria of master data is limitation in short text of various master data elements such as material description, equipment description, maintenance notification description, phrase description. Material description can not go beyond 40 characters. Phrase text (SAP EHS Module) can not go beyond 100 characters. Many time core team members give a master data in MS excel sheet with more than 40 character in these fields. At that time you can make use of LEN function in MS excel to find out the character length.

Other then LEN you can also use TRIM Function in MS excel for master data cleansing.

LEN Function in MS Excel:

LEN function fetches the number (count) of characters in a text string.

Syntax:

LEN(text)

In this syntax field text is mandatory. The text whose length you want to find. It is important to note that “Spaces” count as character.

Let us learn how to apply this formula in MS excel.

Step 1: Put the cursor in the next to the cell for which you want to calculate text length. Then click on formula button. A small screen will appear. On this screen you will have to select the formula LEN and click OK button.

LEN FormulaStep 2: Afterwards a new small window will appear. Here enter the cell number where you have the text string. in the result you will get the text length. LEN Function

Step 3: Now result will appear in the cell where you have applied the formula.

LEN Function in excelYou can also take necessary action to reduce the length of text string and see the character length. In the below screen second row you can see that material description is corrected and now its length is within the permissible limit.

LEN Formula in excel1

In this way you can use this excel formula in master data cleansing.

I request you to share this article on facebook, linkedIn and twitter. This will help and empower SAP Consultants to perform better in master data handling. You can help other by sharing the knowledge.

If you have suggestions to improve this article then put a comment below. I will add your suggestions to improve the article.

4 Comments

  • Bastiaan van Gils • November 5, 2015 •

    I would also use conditional formatting to color a field e.g. red if the length is longer then a maximum of characters. Conditional formatting can also be used to make logical checks on fields, comparison of fields etc. Core team members can then easily see which fields are incorrect and need to be adjusted.

    • Mishra, Vaibhav • November 5, 2015 • • Author

      Thanks Bastiaan. I agree with your comment. Colors in MS excel sheet has a better impact on viewer. In fact I was thinking of writing an article on conditional formatting. Conditional formatting has number of usage in MS excel. We can not only use it in master data cleaning activity but also in ticket/ incident handling. For example : We can make all aged incidents in a support project as red using conditional formating.
      Good catch. 🙂

  • Ron • November 3, 2015 •

    Depending on how your data gets into Excel, the CLEAN and TRIM functions are also useful as they can remove unprintable control codes and extra spaces at the front and end of your text strings.

Comments are closed.

error: Content is protected !!