Paragraphs and bulleted list using text to columns

Copper Contributor

I have a column in a spreadsheet with 2500 plus rows.  Within the column, is basic job description information.  So you have elements Job Summary, Job Description, Primary Responsibilities with various billeted lists separated by hard returns, Education Information, Preferences, and Skills and Competencies.

 

I need to parse the column so that the paragraphs stay together  into new columns.  So for example, I need to move Job Summary into a new column, Job Description into a new column, Primary Responsibilities into a new column (This includes each billeted list for every job), Education Information into a new column, etc.  

 

I can separate into a new column based on the Hard Returns within the file, but it separates the primary responsibilities into a separate column for each bullet on the job.  This means that I have to concatenate it back together and this becomes difficult because some jobs have 4 bullets while others may have 15 bullets.  I am trying to find a way to separate to keep the major sections together based on m

11 Replies

@lostvol 

Could you please submit sample file with dozen or so rows (not 2500+) to illustrate what do you mean.

@Sergei Baklan Here is two rows of data from the spreadsheet.  From the spreadsheet, you can see it is a basic job description all in one cell with Excel.  I want to separate the one cell into multiple columns based on the major sections within the job description.  

 

So I want the opening paragraph in one column, the Primary Responsibilities in the next column, Requirements in the 3rd column, Preferences in the fourth column and then Skills and Competencies in the 5th column.

 

The data greatly varies for each job description.  So you could have 3 bullets in one primary responsibility and in in another job you could have 15.  

 

Let me know if that helps.

@lostvol , I used Power Query to get the information. Please add a few more Job Descriptions to the table in the "JobDescriptions" tab. Just paste in the rows starting A4 and the Table will automatically expand. Then click on Data->Refresh All to update the "Summary" tab. Let me know if this is what you are looking for.

@TheAntony This absolutely would work, but I am receiving an error message.  I just went ahead and posted all the descriptions in the file for you to see the error that I am receiving.

 

This so freaking awesome if you can help me get this working....I have been trying for days to make this work and you did it in a matter of minutes.  

 

Let me know if you can make this work.  

I have tried with a few descriptions and all of the descriptions, but I am receiving the same error message when it tries to refresh.

Expression Error - 5 arguments were passed to a function which expects between 2 and 4
It looks like maybe because not all job descriptions have all the sections. Can that be coded around? So if a section is not found, skip. If not, this is much better than what I had to begin with.

@lostvol1420 , the job descriptions weren't "clean". There were 14 empty ones. Also there are a few 100 items that don't conform to the structure the others are in. I fixed a few of the issues, but review thoroughly for the exceptions.

Yeah, that is definitely part of the issue. The data is anything but consistent. Thank you so much.....I will review and we can then work off this list versus having to cut and paste off the 2500 sections.

@lostvol1420 , correct. Now this will run without errors, but will not have the correct content in the correct column if a Job Description isn't coded correctly. By that, I mean it should have the Section Headers of Requirements, Primary Responsibilities, Preferences, etc. Once you fix those in the data, do a refresh and it will pull through all the items.

I can’t thank you enough for your help on this. Part of this project is to help make them consistent and get the information all out of one field. Your help will enable us to do that.

Thank you so much.

@lostvol1420 

I'm not sure what is the goal, but I'd suggest to separate not only by columns but as rows as well. Please check in attached file.

image.png