VBA code to unmerge/split multiple data in single cell to multiple cell needed

excelsishya

Board Regular
Joined
Jul 25, 2010
Messages
107
Hi Guys,

I am converting Large Pdfs to excel. sometimes i get data merged into single cell.Is there any VBA code unmerge/split only merged data leaving remaining data as it is.

Windows 7
Excel 2007

Thanks in advance

Input
<table width="192" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 120pt;" height="160"> <td class="xl63" style="height: 120pt; width: 48pt;" width="64" height="160">Cash 12 290002
Inv 03 34556676
AR 23 2345765
Oth 34 321</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">AP</td> <td align="right">40</td> <td align="right">400000</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">LTB</td> <td align="right">45</td> <td align="right">10000000</td> </tr> </tbody></table>
Desired Output

<table width="192" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">Cash</td> <td style="width: 48pt;" width="64" align="right">12</td> <td style="width: 48pt;" width="64" align="right">290002</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Inv</td> <td align="right">3</td> <td align="right">34556676</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">AR</td> <td align="right">23</td> <td align="right">2345765</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Oth </td> <td align="right">34</td> <td align="right">321</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">AP</td> <td align="right">40</td> <td align="right">400000</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">LTB</td> <td align="right">45</td> <td align="right">10000000</td> </tr> </tbody></table>
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,963
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top