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
P. S.: Not tired of reading blogs? Support this one by Lars Hvam.
Now that you have mentioned it, I think there wasn't any new post about an Excel file generator for more than a month...
There's a powerful feature in Excel named Power Query Editor to help you doing the job, no need to know formulas:
and so you obtain what you expect without need to know anything 🙂
there is function STXT or the following old technique :
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.