Use "Text to Columns" in code? 2

[BC98121]

Hello. I have been tasked with cleaning up an old spreadsheet with over 14,000 rows of data. Essentially the spreadsheet was designed to track when something was done, and the initials of the person who performed the action. When the spreadsheet was first used both the initials and the date were put into the same column. Eventually that practice was stopped and the initials were put into a new column to the left of the date. Where initials exist in the date column, I need to separate them out of the date column and put them into the Initials column.

BEFORE EXAMPLE:
Initials Date
ab 1/1/2000
ab 1/2/2000
bc 1/10/2009
ab 1/3/2000
ab 1/4/2000
bc 1/11/2009

AFTER EXAMPLE:
Initials Date
ab 1/1/2000
ab 1/2/2000
bc 1/10/2009
ab 1/3/2000
ab 1/4/2000
bc 1/11/2009

As the example shows, this pattern is not contiguous, so it would be hard to use "Text to Columns" for this. The cells are also part of an array (which I don't fully understand), so I cannot sort by date.

Is there a way to automate running "Text to Columns" and specify it for only those cells where the "Initials" value is blank? It seems like a simple If/Then kind of thing, but I am basically new to VBA so I'm not sure where to start.

Any ideas would be greatly appreciated.

Thank you.

 

[SkipVought]

Hi,

Use the DELIMITED option with a SPACE delimiter.

I would SORT the table first to get all the TEXT data together, as that is the only data you want to parse.

You really do not need any VBA code. Could be done ON THE SHEET, in less than 15 minutes, if I understand the problem.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Of course Skip is correct but if I was attempting this in VBA I would either
* loop through use the Split function (look it up in vba help)
or
* merely record doing what skip has proposed.

Gavin

 

[SkipVought]

Gavone,

This "sounds" like a one-time cleanup shot.

I wouldn't suggest, a novice invest the time in a VBA project, when a simple and QUICK solution is on the sheet.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[BC98121]

Thanks Skip & Gavona. Skip is right, this is a one-time cleanup. And I have already wasted a couple of hours today trying to figure out the VBA.

I guess one problem I had was with my sort, as I was trying it with "Sort A to Z" and running into the array error. Sorting through the "Sort" dialog box did work to isolate the text data and then Text to Columns did work. Thanks.

 

[Gavona]

Well Skip, I sort of agree, but there again it was posted in the VBA forum. And OP did seem to have a reluctance to sort the data ("so I cannot sort by date").
Partly I posted because when I did need text to columns type functionality in code it took a while to discover the split function - but that is what solved my issue and it might therefore help others attracted by the title of this thread.

Regards
Gavin

Gavin

 

[SkipVought]

I missed the Array part.

To defeat that problem, I'd COPY the DATE column and then Edit > Paste Special -- VALUES

The proceed with Sort et al.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}