3 MS Excel Functions, every SAP Consultant must know

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.

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.

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.

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

Finally result will appear in the Cell A2.

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.

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.

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

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.

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.

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.

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

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