Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Bullets and su bullets in a single cell

0

Hi, 


We have around 100 workbooks. Each workbook has a table. Each table grid has 100s of cells. Every cell has some bulleted text. Sample is given in attached excel in "Input text" sheet (I gave only 1 cell here but there is a table of such cells).

The problem is that these excels needs to be uploaded using a tool. However, that tool does not accept this format. It needs text in the format I gave in 2nd sheet (Desired Output).

Therefore, I now need to modify all these excels and which needs lots of manual work. This will lead us to lose our productive time.

Is there any way to sort this out using PowerQuery/VBA?

excel
MS Office Version: 365
posted by: Dheer on Sat Jun 1, 2019 at 9:10 pm
Answer
Discuss

Discussion

Excel isn't a text processor. Consider using Word.
Excel designed to handle each bullet and each sub-bullet in its own cell. If you must use Excel at all consider giving your data a format properly supported by Excel.
If your tool doesn't support the input format your data have, consider modifying the tool. It rarely is a good idea to modify data. Data should be as near to their original state as they can be kept.
With that said, VBA could be used to convert the data but the examples you give are insufficient because they are look-alikes. The Input text looks like your example but the actual data may be different. The dash may not be a minus sign, the blanks may not be Chr(32). The (maximum) length of each line isn't known. That a blank line precedes a new bullet is my guess, as is that a Carriage Return signals a new sub-bullet (meaning there are no CRs within bullets or sub-bullets.
The desired output replaces dashes with numbers. The presumption therefore is that your tool rejects the dash. There is a very good chance that it actually rejects something quite different. Therefore it's rather uncertain that your tool would accept any format that looks like your sample.
Variatus (rep: 4889) Jun 2, '19 at 12:51 am
Unfortunately, I am no one to change the tool. 
Tool is created by client and need data in the format I mentioned in output sheet.
A team has created these files
without knowing the actual format that client will need. Now they want me to change excels in desired format and it is totally manual task if no automation is possible:(
Dheer Jun 2, '19 at 1:03 am
If there is no way for you but to persist with Excel and bullets in each cell please expand your question to include a few samples of actual data (critical words can be replaced with "xxxxx" of same length). It would be useful if the owners of the tool could confirm that the actual sample of the desired output which you submit here was tried and does work.
Variatus (rep: 4889) Jun 2, '19 at 1:20 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login