Split content on new sheet and extract info in parentheses

[tsalaki]

Hi all,

I have a table on one sheet which contains a comment column with info similar to the following:

where * number = quantity and ** string = product description

and I would like to copy that info to another sheet where it will be split in different rows like:

How can I do that using a formula? Keep in mind that each string - number separated by a comma on the comment column.

Thanks in advance
Regards

 

[DanteAmor]

Hi and welcome to MrExcel.

Change Sheet1 to the name of the sheet where you have your data.

Put the following formula in A2 of sheet2:

=CHOOSECOLS(TEXTSPLIT(TEXTJOIN(",",,SUBSTITUTE(Sheet1!A2:A5,")","(")),"(",",",1),4,2)

 

[tsalaki]

Hi,

Thanks a lot for your reply. It looks like the formula is not working. I am getting the below error:

Do you know what is causing this problem in the formula?

Thanks in advance
Regards

 

[tsalaki]

Hi,

I finally made the formula work! Thanks a lot! is it possible on the final output to make also a sum of the total quantity per product? In the above table the Description column contains a list of products with different quantities. Can I have the total quantity per product on each row?

Thanks a lot for your help! Really appreciated!

 

[DanteAmor]

With the results obtained, you create a pivot table.
Ex:

 

[tsalaki]

Good morning,

Thanks for your reply. Pivot table works but all the total quantities are zeros. Looks like the column that created with the formula with the quantity per product it is not recognized as a number in order for the sum function to work properly, or something like that

 

[Peter_SSs]

@tsalaki

Can you give some sample data and expected results with actual strings and actual numbers and in a form that we can use to test with?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[tsalaki]

Hi,

Thanks a lot for your reply. I tried to install the XL2BB add-in in Excel but it seems like due to company policy there are some features disable on that add-in.

So I am not sure that I can use it. The problem mainly is that the data extracted by the formula

=CHOOSECOLS(TEXTSPLIT(TEXTJOIN(",",,SUBSTITUTE(Sheet1!A2:A5,")","(")),"(",",",1),4,2)

haven't the correct format. Despite the fact that I have formatted all the column containing the quantities / product as a number this is not actually change the result of the formula to real number. Looks like it remains as a text and probably that is causing the zeros on pivot table sums. I need to add the value function in the above formula somehow to define that the content inside the parenthesis which corresponds to the quantity of the product is a number and not text.

Hope that know is clear to you. I can attach real data as a screenshot here but I don't think that it will help you more.

Thanks in advance

 

[tsalaki]

Hi,

I finally made this work as I want. I copied the contents from formula columns to another columns in the sheet and use the value function on the quantities in order to convert the text from the formula to real numbers and then create a pivot table from that range. I hide the columns with the range data in order to show only the pivot table with the total quantities / product and it works as I wanted!

Thanks a lot for your help!
Much appreciated!

 

[Peter_SSs]

Thanks a lot for your reply. I tried to install the XL2BB add-in in Excel but it seems like due to company policy there are some features disable on that add-in.

It may be an issue with company policy, but if you are able to install it all (as it seems you have) then I think it more likely the issue is related to the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page I linked above.

I can attach real data as a screenshot here but I don't think that it will help you more.

I think that it probably would.