This Excel VBA tutorial explains how to convert cell contents into table using Exel VBA.
Excel VBA convert cell contents into table
Yesterday when I tried to copy a table from a website to Excel, all the table contents are pasted into a single cell instead of copying the whole table. I believe there are workarounds to copy table but since this is an Excel blog, I want to write a Macro to convert cell contents back into a table.
Previously I also wrote a post to convert cell contents into rows in one column, this tutorial explains how to convert cell contents into multiple columns and rows to rebuild a table.
Excel VBA convert cell contents into table – Example
Yesterday I found a Pokemon Go table in a website that lists out the max CP of each Pokemon. I tried to copy the whole table to Excel so that I can do sorting.
| No | Name | Max CP |
| 1 | Bulbasaur | 1071.54 |
| 2 | Ivysaur | 1632.19 |
| 3 | Venusaur | 2580.49 |
| 4 | Charmander | 955.24 |
| 5 | Charmeleon | 1557.48 |
| 6 | Charizard | 2602.2 |
| 7 | Squirtle | 1008.69 |
| 8 | Wartortle | 1582.79 |
| 9 | Blastoise | 2542.01 |
| 10 | Caterpie | 443.52 |
| 11 | Metapod | 477.92 |
| 12 | Butterfree | 1454.94 |
| 13 | Weedle | 449.09 |
| 14 | Kakuna | 485.35 |
| 15 | Beedrill | 1439.96 |
| 16 | Pidgey | 679.93 |
When I copied the website table to Excel, all the contents are pasted in one cell (A1), each data is separated by a space.
In order to convert A1 cell contents into table, press ALT+F11 to enter into VBE, insert a new Module, paste the below code
Public Sub convertTable()
Set targetRange = Range("A1") 'define the data source to be processed
targetDelimiter = " " 'define the separator
noOfCol = 3 'define the number of columns in the table
noOfItems = UBound(Split(targetRange, targetDelimiter)) + 1
noOfrows = noOfItems / noOfCol
itemNo = 0
For r = 2 To noOfrows + 1 'define which row the table is to be created
For c = 1 To noOfCol
Cells(r, c).Value = Split(targetRange)(itemNo)
itemNo = itemNo + 1
Next c
Next r
End Sub
In the above example, I have specified the data range is in Cell A1, data is separated by space, and the table contains 3 columns. If the data itself contains space, remove it manually first.
Run the Macro, now you get the table starting from row 2.
For your interest, click here to download pokemon max CP table.
