If your organization uses the add-in, you don't need to manually clean the data.
: Go to the Components tab. You can toggle between "Show Hierarchy" (stepped) or "Flat Presentation."
: Sometimes SAP adds a ' before numbers. Use Find and Replace (Ctrl+H) to find the apostrophe and replace it with nothing, or use the Text to Columns wizard on the Data tab to reset the formatting. Pro-Tips for SAP Data Sap Hierarchy Excel
Example: =LEN(A2)-LEN(TRIM(A2)) will tell you how many leading spaces exist, which usually corresponds to the hierarchy depth.
If you don't want to use Power Query, you can use formulas to "flatten" the hierarchy manually. If your organization uses the add-in, you don't
: Select the column, click the yellow warning icon, and select Convert to Number .
: SAP hierarchies often show the same "Leaf" node under multiple "Nodes." Use Data > Remove Duplicates if you are trying to get a unique list of accounts. Use Find and Replace (Ctrl+H) to find the
: If SAP exports levels in a single column with spaces (indentation), use Split Column > By Number of Characters or By Delimiter to separate levels into distinct columns. 2. Standard Excel Formulas