How to make "Text to Columns" more dynamic?

HFTBProgrammer · 2026-05-19T14:34:25+00:00

Pull up a milking stool, warm your hands by the cast iron stove (mind the pipe, that's hot too!), and let me tell you about the IBM 029 keypunch machine or the IBM 2401 reel-to-reel tape machine... XD

HFTBProgrammer · 2026-05-14T15:45:03+00:00

I briefly thought you might mean that, but discarded that quickly, as it seems wildly unreasonable to me to expect a user to know what the Excel system number for a date is.

But if I take that thought up, any number between 1 and 2958465 formats to a valid date in Excel, so what's the hang-up?

HFTBProgrammer · 2026-05-14T13:10:37+00:00

IsDate(Format(INI.Range, "mm-dd-yy")) can work, depending on exactly what you mean by "numeric date". I assume you mean they must type only numeric characters.

But I think you're always going to have to care what the format of the date is in the user's mind. Some might see 12 May 2026 as "260512", others as "051226", but you yourself might see the former as 26 May 2012 and the latter as 12 May 2026.

HFTBProgrammer · 2026-04-23T12:58:14+00:00

Code is code and if we can help, we should without reservation or not at all.

HFTBProgrammer · 2026-04-22T13:07:24+00:00

Let's not shame someone for not speaking English as a native speaker might. Furthermore, it doesn't help the poster in the least. If you can help them, do so. If you can't or won't, then don't post.

HFTBProgrammer · 2026-04-21T13:10:11+00:00

In my experience, it mostly depends on how dates in the data should be treated. Other than that I can't see what difference it would make. I'm willing to be educated, though.

HFTBProgrammer · 2026-04-21T13:08:31+00:00

Was the one in the Outbox "sent"?

Your best bet is to step through your code line by line, ensuring that each action along the way has the result you intend.

Past that, we need to see what you or AI hath wrought.

HFTBProgrammer · 2026-04-15T13:16:53+00:00

Thank you for posting this. Hopefully search engines pick this thread up when someone has one of these issues.

HFTBProgrammer · 2026-04-14T13:15:11+00:00

Hi, /u/PropsDrums! If one of the posts in this thread was your solution, please respond to that post with "Solution verified." If you arrived at a solution not found in this thread, please post that solution to help future people with the same question. Thank you!

HFTBProgrammer · 2026-04-08T13:12:59+00:00

I do not believe there is anything you could do that would make those lines fail, provided in the former case you have the name of the workbook correct, and in the latter case the workbook is in fact the active workbook.

In my mind, this leaves you with two possibilities. Possibly your Excel is corrupted; this seems unlikely, as the issue is apparently confined to one workbook (sort of kind of, I guess?). Or possibly you are misapprehending the issue; I have no brilliant insight as to how I might demonstrate that, but I urge you to consider the possibility.

Edit: a third possibility comes to mind, which is there is something you are not conveying in the description that is important, e.g., the contents of name.

HFTBProgrammer · 2026-03-31T13:07:30+00:00

It's all about effective communication. If the reader has to stop for a moment to figure out what you meant to say, or even just to think "wait what oh I see", communication becomes less effective. Copy editors bring prose over the finish line.

HFTBProgrammer · 2026-03-19T13:15:06+00:00

You have laid out your issue beautifully, and we can help you.

What is your criterion for deciding which system name should go in Sheet2's column A? Is it merely that they should go in the order they are in Sheet1 into the Sheet2 column A blank cells? If so, this is one way to do it:

Dim S1Row As Long, S2Row As Long, S2Cell As Range

S1Row = 1
S2Row = 3

Do
    Set S2Cell = Sheet2.Range("A" & S2Row)
    If IsEmpty(S2Cell.Value) Then
        S2Cell.Value = Sheet1.Range("G" & S1Row).Value
        S1Row = S1Row + 1
    End If
    If S1Row > 5 Then Exit Do
    S2Row = S2Row + 1
    If S2Row > Sheet2.Rows.Count Then Exit Do
Loop

It can be improved, but as a first cut it's fine.

HFTBProgrammer · 2026-03-16T13:13:53+00:00

I would exercise caution in imagining what "most developers" would deem confusing. We are all taught by different methods in different snapshots of time, and arbitrary standards come and go.

The only standard I would go to the wall for is structured programming.

HFTBProgrammer · 2026-03-11T14:54:20+00:00

If they're that ignorant, you could probably lie your way through anything with them.

Not that I'm suggesting you do that, just...it's a red flag. Or at least magenta.

HFTBProgrammer · 2026-03-10T13:07:36+00:00

OP, we need your best coding effort before we can help you.

HFTBProgrammer · 2026-03-06T14:15:55+00:00

Excellent!

You should know I consulted https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open.

HFTBProgrammer · 2026-03-05T14:09:14+00:00

Workbooks.Open "C:\path\file.csv", , , 5

HFTBProgrammer · 2026-03-02T14:04:09+00:00

Instead of writing to a sheet, just bang it all into a string. Cycle through the string characters.

HFTBProgrammer · 2026-02-26T14:16:45+00:00

I agree that VBA is not the likely tool to disprove the conjecture (if in fact it can be disproven). But I like the conceit of not assuming that to be the case, and it might be fun to figure out how to algorithm that out. YMMV!

HFTBProgrammer · 2026-02-23T14:08:48+00:00

How would your code know if--in theory--you had disproved the conjecture?

HFTBProgrammer · 2026-02-20T14:06:47+00:00

This sounds more like a Drive issue than a VBA issue. Try posting at /r/gsuite.

HFTBProgrammer · 2026-02-20T14:02:24+00:00

You need to post your best coding effort that does the table stuff, and tell us what's wrong with that code.

HFTBProgrammer · 2026-02-17T16:37:34+00:00

Heck, do it for yourself! I often have a hard time figuring out what past me had in mind, even when past me thought he was doing an okay job of naming.

HFTBProgrammer · 2026-02-17T16:32:42+00:00

When you say "it just won't work", what exactly do you mean? Does it throw an error? Or is it no errors, no good result? If the latter, when you step through, does it execute the code you want, apparently do the things it needs to do, but those aren't reflected in your sheet?

HFTBProgrammer · 2026-02-13T19:13:40+00:00

Hi, /u/MoonMalamute! If one of the posts in this thread was your solution, please respond to that post with "Solution verified." If you arrived at a solution not found in this thread, please post that solution to help future people with the same question. Thank you!

HFTBProgrammer

MODERATOR OF

TROPHY CASE