By -

To convert a column of folder paths into a tree structure of rows, you can use a combination of Excel formulas and VBA code. Here are the steps to follow:

  1. Insert a new column to the right of the column with the folder paths.
  2. In the first cell of the new column, enter the formula =LEN(A1)-LEN(SUBSTITUTE(A1,"\","")), where A1 is the first cell in the column with the folder paths. This formula calculates the number of backslashes in the folder path.
  3. Copy the formula down the entire column to calculate the number of backslashes in each folder path.
  4. Insert a new sheet in the workbook and name it "Tree".
  5. In the "Tree" sheet, create headers for the columns "Level 1", "Level 2", "Level 3", etc. up to the maximum number of levels in your folder paths. For example, if your folder paths have a maximum of 3 levels, create columns for "Level 1", "Level 2", and "Level 3".
  6. In the first cell of each column, enter the corresponding level number. For example, in cell A1, enter "1" for Level 1.
  7. In cell A2 of the "Tree" sheet, enter the formula =LEFT(Sheet1!A2,FIND("|",SUBSTITUTE(Sheet1!A2,"\","|",B2))-1), where Sheet1!A2 is the first cell in the column with the folder paths, and B2 is the level number in the "Tree" sheet. This formula extracts the first level of the folder path.
  8. Copy the formula across the columns for all levels of the folder path.
  9. Copy the formulas down for all rows in the "Tree" sheet to create a tree structure of rows for all folder paths.

Note: To make this process more automated, you can create a VBA macro that loops through the column with the folder paths and populates the "Tree" sheet with the appropriate level values.

Anandan is a SharePoint and Microsoft Azure consultant.He is having 9+ years of experience in the web, cloud, Machine Learning, IoT and mobile technology. He handled multi-platform solution as a consultant with the experience of designing, developing and deploying solutions by leveraging Microsoft, IBM, Apple, and Google technology stack, has played a dynamic role in the development of SharePoint and ML technologies. He writes articles related to SharePoint Online/On-Premises and Machine Learning.

Comments

Post a Comment

Popular posts from this blog

Bulk update and delete using SPservice in SharePoint

By -
$().SPServices.SPUpdateMultipleListItems({ listName: "States" , CAMLQuery: "<Query><Where><Eq><FieldRef Name='Status'/><Value Type='Text'>Active</Value></Eq></Where></Query>" , valuepairs: [["Status", "Inactive"]] }); This call to SPUpdateMultipleListItems will update all of the items in the States list which have their Status = "Active", setting their Status = "Inactive". $().SPServices.SPUpdateMultipleListItems({ listName: "States" , CAMLQuery: "<Query><Where><Eq><FieldRef Name='Status'/><Value Type='Text'>Inactive</Value></Eq></Where></Query>" , batchCmd: "Delete" }); This call to SPUpdateMultipleListItems will delete all of the items in the States list which have their Status = "Inactive".
Read more
By -
Image
  How software helped world during the pandemic The COVID-19 pandemic has changed the way we work, study and socialize. In particular, it has accelerated the trend towards digitalization. Many organizations have had to adopt new technologies to stay afloat during these difficult times. Here, we take a look at how different software applications have helped people and businesses during the pandemic. How did software help during the pandemic? Software has helped the world in a number of ways during the pandemic. Perhaps most importantly, it has allowed people to stay connected when they otherwise would have been isolated. It has also been a valuable tool for research and data collection, helping to inform decision-makers about the best course of action to take. In addition, software has played a role in the development and distribution of vaccines and treatments. And it will continue to be an important part of the response to the pandemic, helping us to track the spread of the di...
Read more

Javascript function to populate from query string

By -
 function populateFieldsFromQueryString() {   // Get the query string from the URL   const queryString = window.location.search;   // Check if there is a query string   if (queryString) {     // Remove the leading "?" character     const queryParams = queryString.substring(1);     // Split the query string into key-value pairs     const keyValuePairs = queryParams.split("&");     // Loop through the key-value pairs     keyValuePairs.forEach((pair) => {       // Split each pair into key and value       const [key, value] = pair.split("=");       // Decode the key and value (in case of URL encoding)       const decodedKey = decodeURIComponent(key);       const decodedValue = decodeURIComponent(value);       // Find the corresponding field in the document       const field = document.querySelecto...
Read more