Skip to Content
Technical Articles

Practical use of Microsoft Excel

Dear community, even if the heading suggests: Today we are not talking about another Excel generator for ABAP 😉 It’s about some very useful features of Microsoft Excel.

One of the features recently saved my day. I had received an email with a lot of messages about bad processed documents. Unfortunately, the document numbers were always contained in strings.

Here’s an example with just five entries. I had a lot more entries. I’ve overdramatized the messages in my example. On the one hand for fun 🙂 On the other hand, I wanted to do it like some “daily newspapers” to get as much attention as possible because of the drama – I don’t like sensational journalism and I also don’t like games with half-truths and open questions!

document 0050120051: incredible mistake while processing
document 0050120052: exorbitantly gigantic error
document 0050120053: indescribable failure situation
document 0050120054: final exceptional state
document 0050120055: Guru Meditation #0100000C.00FE800

The good thing about the many messages was that they all had the same cause, which was easily eliminated. But all documents had to be processed again. There was also a program to process them again, with multiple selection for the document numbers. However, I needed a list of the document numbers. Typing in the document numbers by hand or individually using copy & paste looked like imposition 🙁

That’s why I came up with the “MID” function (in German called “TEIL“) of Excel. With the function I was able to extract the document numbers from the text in column “A” as single values in column “B”. Actually nothing special, but I was very happy 🙂

In addition to this function, there are certainly more practical functions. What are your experiences?

 

Best regards, thanks for reading and stay healthy

Michael

 

P. S.: Not tired of reading blogs? Support this one by Lars Hvam.

/
4 Comments
You must be Logged on to comment or reply to a post.
  • There's a powerful feature in Excel named Power Query Editor to help you doing the job, no need to know formulas:

    • enter your texts in a sheet
    • select the texts
    • menu Data > from Table/Range ; that starts Power Query Editor
    • I don't remember the exact menu, but you may "create a column from the examples" in which you start to enter the expected values. As soon as Power Query is able to deduce code in Microsoft M language, it automatically applies this code to the other lines
    • You can then return the result to Excel

    and so you obtain what you expect without need to know anything 🙂

  • there is function STXT or the following old technique :

    • Paste your text in a column and click button "Text to columns" in the tab "Data"Choose%20in%20tab%20DATA%20the%20button%20Text%20to%20columns
    • In the pop up, move the arrows with the mouse
      Choose%20column%20width
    • Choose the type of each column and click on "Terminate" (=Finish)
    • The result (to keep the leading zeroes you should have choose format text in the previous step)Result
    • Thank you for this solution. I think I've used this function before when opening CSV files. As little note for all readers: Excel function STXT is as far as I know the french name of function MID. The Excel function names are language dependent.