×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Use "Text to Columns" in code?
2

Use "Text to Columns" in code?

Use "Text to Columns" in code?

(OP)
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.

RE: Use "Text to Columns" in code?




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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Use "Text to Columns" in code?

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

RE: Use "Text to Columns" in code?




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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Use "Text to Columns" in code?

(OP)
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.

RE: Use "Text to Columns" in code?

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

RE: Use "Text to Columns" in code?




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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close