VBA Macro: How to split using TextToColumn without period as delimiter

L

Legacy 477460

Guest
I have a list of data with multiple IP addresses in a single cell.
I use the following VBA code to split the IPs into separate cells.


For Example,
10.10.33.187 10.10.48.6 10.10.33.188
10.56.100.77 172.22.210.253
172.22.210.3 172.22.210.6 172.22.210.7 172.22.210.8

gets converted into,

10.10.33.18710.10.48.610.10.33.188
10.56.100.77172.22.210.253
172.22.210.3172.22.210.6172.22.210.7172.22.210.8


However, when I run the code again, it also splits each IP into separate numbers.
For example,
10.10.33.187

gets converted into,

10103187

I know that this can be done using simple excel function.
However, I need VBA code for a larger task of which this is just one step.
So can someone please help me with preventing the splitting of period (full stop)?
I do not see period (full stop) being used as a delimiter in the code.
So why is it splitting it at the periods?



VBA Code:
Sheets("Hosts").Activate
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Could you not just use a space as a delimiter?

1618698043133.png
 
Upvote 0
I'm afraid I can't recreate the results you describe above. That said, I do recall a time when I was trying to paste data into a spreadsheet that constantly kept on automatically splitting at the colon (ie. term: definition). I think I concluded that this sheet in particular had some kind of 'memory' on how it wanted to split data and I ended pasting it into a new workbook (which I think may have worked). That doesn't help you though - without being able to reproduce the issue you describe, the only thing I can think of is perhaps trying pasting it / processing it programmatically?
 
Upvote 0
My question is: even though I don't use period as a delimiter, why does it split the IP along the period.
Yes, I think it remembers it, and since the "Other" argument is not part of your code, it is not removing it.
Try changing it like this:
Rich (BB code):
Sheets("Hosts").Activate
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False
 
Upvote 0
Solution

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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