3 MS Excel Functions, every SAP Consultant must know

3 MS Excel Functions, every SAP Consultant must know
5 (100%) 1 vote

3 MS Excel Functions, every SAP Consultant must know

I am writing this article to provide a simple solution to the issue of data handling in FTP.

Suppose I send data to a third party system using FTP (File transfer protocol). At that time system creates a file with data. This file gets copied in the directory (t code-AL11). This output looks like a newspaper. It’s very difficult to handle this data. Even if I know that first 10 character represents some field, it’s very difficult to separate out data.

In such a situation we can make use of these three formulas:

  1. LEFT
  2. RIGHT
  3. MID

Every SAP Consultant must know LEFT, RIGHT and MID MS Excel functions.

lmr

LEFT: LEFT formula fetches the first character or characters in a TEXT, based on the number of characters you specify in the formula.

Syntax: LEFT (text, [num_chars])

Let us understand the meaning of each field:

Text: It is a mandatory item in this formula. It is the text string in which we want to fetch the left most characters.

Num_Char: This field is optional. I can enter the number of characters which I want to fetch. If I keep this field as blank then it is assumed as ‘1’.

For ex: If PO number, PO item and Material are clubbed together and one cell of excel sheet then we can use LEFT formula to fetch PO no. Please refer below screen shot.

Left Function

In the next screen you can see that I have applied the formula to fetch the first ‘9‘ characters of the text string. First click on ƒx button, then a pop window will appear. Select the formula and click OK button as shown below.

Left Function1

Now apply the formula as shown below to fetch PO no. from the text string.

Left Function2

Finally result will appear in the Cell A2.

Left Function3

In this way you can apply LEFT function and fetch ‘n’ number of initial characters in a text string.

RIGHT: RIGHT formula fetches the last character or characters in a TEXT, based on the number of characters you specify in the formula.

Syntax: RIGHT (text, [num_chars])

Let us understand the meaning of each field:

Text: It is a mandatory item in this formula. It is the text string in which we want to fetch the right most characters.

Num_Char: This field is optional. I can enter the number of characters which I want to fetch. If I keep this field as blank then it is assumed as ‘1’.

For ex: If PO number, PO item and Date are clubbed together and one cell of excel sheet then we can use RIGHT formula to fetch Date. Please refer below screen shot.

Right function in MS excel

In the next screen you can see that I have applied the formula to fetch the Last ‘10 ‘ characters of the text string. First select cell no. B2 and click on ƒx button, then a pop window will appear. Select the formula and click OK button as shown below.

Right Formula

Now a new small window will open here I will have to fill the data as shown in the screen.

Rigfht formula apply

Right function result

In this way you can apply RIGHT function and fetch ‘n’ number of initial characters in a text string.

          3. MID: MID formula fetches a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

Syntax: MID (text, start_num, num_chars)

Let us understand the meaning of each field:

Text: It is a mandatory item in this formula. It is the text string in which we want to fetch the characters.

Start_num: It is also a mandatory item in this function. The position of the first character I want to fetch in the text string. The first character in text has start_num = 1, and so on.

Num_Char: This field is mandatory. Specifies the number of characters I want this function to return from text string.

For ex: If PO number, PO item and Date are clubbed together in one cell of excel sheet then we can use MID formula to fetch PO item. Please refer below screen shot.

Right

In the next screen you can see that I have applied the formula to fetch the middle ‘4‘ characters of the text string. First select the cell and click on formula button. Then select MID formula and click OK button.

MID Function

Next step is very important and confusing.

When I click on OK button a new pop window will come and there will be three fields to fill up.

In the first field you will have to fill up the Cell no. of excel sheet where you have the text string.

In the second field start_num, I will fill up the number of characters which you want to skip from the beginning of text. For example, In this case we want to skip first 10 characters of PO no., therefore, we will enter ’10’ in this field.

In the third and last field Num_chars, I will enter the number of characters I want to fetch of desired text. For example, in this case I want to fetch 4 characters of PO item, so I will enter 4 here.

Please refer below screen shot.

MID Function Result

After applying the formula the final result will have the PO item.

MID formula result

 

In this way you can apply MID function and fetch ‘n’ number of initial characters in the middle of a text string.

If you like this article then please leave a comment below. Give a like on facebook (Click here to like) and join my group in LinkedIn (Click here to join).

 

 

 

Vaibhav

Author: Vaibhav

Vaibhav is an engineer by schooling and an SAP Consultant by training with 12 years of experience. He is certified in SAP PM and SAP MM. His expertise includes SAP PM, SAP MM, SAP QM, and MS excel. His industry specialties are Chemical, Automobile, Electronics and Infrastructure. He has lots of experience in SAP Training and Consulting.

How drop down list in excel can help you?   Almost every SAP consultant had
Learn IFERROR in excel in less than 5 minutes Time to say good bye to #N/A,
5 Effortless tricks to handle Duplicates in Excel This is going to be the most
Shortcut keys in MS excel to improve your efficiency at work   Here is another
Innovative use of AutoCorrect option in MS Excel You must be wondering after reading the

5 Comments

  • AJIT SAMBHUS February 7, 2017 Reply

    Hi,
    I had experienced the pain of separating Texts manually–This will help me to understand & work

  • Lisa Monahan October 22, 2015 Reply

    Thank you for sharing. This information will be helpful.

    • Vaibhav
      Mishra, Vaibhav October 23, 2015 Author

      Lisa, glad you found the information valuable and helpful. Looking forward to hear more feedback from you.

  • Deepak Kumar October 19, 2015 Reply

    Nice Article Your way of writing article is awesome, it easily can understand with help of images.

    • Vaibhav
      Mishra, Vaibhav October 20, 2015 Author

      Thank you Deepak and all the best to you… 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!