VBA for Text To Columns to correct how imported data is stored

diversification

New Member
Joined
Jun 24, 2020
Messages
40
Office Version
  1. 365
Platform
  1. Windows
Hi there, I do a lot of Index Match type of stuff where I'm cross-referencing reports exported from multiple sources, and trying to match account numbers. Some of the account numbers are entirely numeric, while some are alphanumeric, so matching frequently breaks because of the way the data is exported. My solution has consistently been to import the data, then highlight the column containing the account numbers and use the Text To Columns feature, choosing delimited, deselecting all delimiters, and using General data format. The result is that the numeric account numbers store as numbers and the alphanumeric account numbers store as text. If I do this to all imports, it ensures that my Match functions work properly.

But... I'm getting tired of the extra clicks and want to do some automating. I'd like to put together VBA code that I can bind to a clickable button on the relevant sheets, which will execute the same type of conversion on the column in question. I use a lot of Tables, so ideally I'd like VBA that I can aim at either (1) a specific column, or (2) that I can aim at a specific Table column.

I'm VERY green writing VBA, but here's an example of code I use to clear a table before pasting in new data. I'm after something like this that I can bind to a key. I'm envisioning something similar where I'm basically just substituting in the Text To Columns (or other suggested code) in place of the ClearContents line.

VBA Code:
Sub WipeSheet()
'
' WipeSheet Macro
' Wipes all entries in the sheet besides formulas
'
' Keyboard Shortcut: Ctrl+Shift+W
'
    PopupPrompt = MsgBox("Do you want to clear this sheet?", vbYesNo + vbExclamation + vbDefaultButton2, "CLEAR SHEET DATA")

    If PopupPrompt = vbNo Then
            Exit Sub
    End If
    
    Range("AcctExportTable[Name]:AcctExportTable[Value]").ClearContents
    Range("$A$10").Activate


End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
See if this does what you need. It relies on you selecting a cell in the column of the table that you want to convert.

VBA Code:
Sub ConvertNumbers_TextToColumns()
    ' User to select a cell in the column that needs to be converted
    ' The Text to Columns will convert numbers from text to number

    Dim tbl As ListObject
    Dim rCol As Range
    
    Set tbl = activeCell.ListObject
    If tbl Is Nothing Then
        MsgBox "No table cell has been selected"
        Exit Sub
    End If
    
    Set rCol = Intersect(tbl.DataBodyRange, activeCell.EntireColumn)

    rCol.TextToColumns Destination:=rCol.Cells(1), _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End Sub
 
Upvote 0
Solution
This solution works perfectly. Thank you!!
 
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,132
Members
453,641
Latest member
enfkkviesm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top