Macro: Use Text-to-Column for Whole Column

[Cherub Wings]

Hello,

I'm attempting to use Text-to-Column with Delimiters Comma, Space, or Colon for Column C. The code does not work using the macro recorder, and after hours of research, the best option I could find was using the code below, but the code only splits cell C2 by spaces, then just stops.

Sample Code:
Dim ttlrowcount As Integer
Dim ttlcount As Integer

ttlrowcount = WorksheetFunction.CountA(Range("C:C"))

Range("C2").Select

For ttlcount = 1 To ttlrowcount

ActiveCell.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, Space:=True
Next
'This is only splitting the first cell C2, but only split the commas...then just stops


Worksheet Example:
'Col A' 'Col B' 'Col C'
Bldg - Room - Name
Bldg 1 - Upstairs - John Michael Smith, Number:653152, Date:6/13/2023
Bldg 4 - Downstairs - Jane Lucy Smith, Number:278659, Date:3/20/2022
Bldg 2 - Upstairs - Michael Dean Johnson, Number:331513, Date:8/22/2021
Bldg 1 - Upstairs - Robert Christopher Jones, Number:289630, Date:1/13/2022
Bldg 3 - Downstairs - William Richard Lee, Number:301992, Date:3/17/2023

Any recommendations on splitting and isolating everything that's in Column C?

Much appreciated,
Cherub Wings

 

[Rick Rothstein]

Your description seems to be saying something different than I think you really want. Using just the first example row of data that you posted, can you show us what should go into Column C, D, E and so on? To make it clear to us, list each column's letter, some delimiter (use space-dash-space maybe) and then what from that first example should end up in that column letter's cell (do that for all the text once broken out).

 

[Cherub Wings]

Hello, Rich -

Please forgive my ignorance. Please see below for the desired results. What I'm eventually trying to accomplish is to isolate the date, like in Col I below, then I want to use a formula to highlight the dates that appear within the next 365 days.

Desired Result:
'Col A' 'Col B' 'Col C' Col D' Col E' Col F' Col G' Col H' Col I'
Bldg - Room - Name -
Bldg 1 - Upstairs - John - Michael - Smith, - Number: - 653152, - Date: - 6/13/2023
Bldg 4 - Downstairs - Jane - Lucy - Smith, - Number: - 278659, - Date: - 3/20/2022

 

[Rick Rothstein]

Do all your names always have 3 name parts (first, middle, last)? I ask because my wife does not have a middle name. If you have someone like her in your data, what should happen... should the middle name cell be skipped over (left blank)?

 

[Cherub Wings]

Do all your names always have 3 name parts (first, middle, last)? I ask because my wife does not have a middle name. If you have someone like her in your data, what should happen... should the middle name cell be skipped over (left blank)?

Yes, exactly...skip over.

 

[Rick Rothstein]

Give this macro a try...

Sub SplitNameColumn()
  Dim R As Long, ColVals As Variant
  ColVals = Range("C2", Cells(Rows.Count, "C").End(xlUp))
  For R = 1 To UBound(ColVals)
    If Not ColVals(R, 1) Like "* * *, Number:*" Then ColVals(R, 1) = Application.Replace(ColVals(R, 1), InStr(ColVals(R, 1), " "), 0, " ")
    ColVals(R, 1) = Replace(Replace(ColVals(R, 1), ",", ""), ":", " ")
  Next
  Range("C2").Resize(UBound(ColVals)) = ColVals
  Columns("C").TextToColumns , xlDelimited, , False, False, False, False, True, False
End Sub

 

[Cherub Wings]

Rick,

You're a genius! Thank you so much for sharing your expertise.

Best wishes,
Cherub Wings