Excel VBA convert cell contents into table

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.convert cell contents into table 01

 

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.

convert cell contents into table 02

For your interest, click here to download pokemon max CP table.

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *