Macro to periodically check cells for delimiter to parse if needed

[ladytexan]

I have a spreadsheet with a lot of values being added. Some of these values are single numbers, some are multiple values in a single cell, delimited by a colon. I need a macro that can run in the background of the spreadsheet, so that whenever a cell is closed after being edited, it checks for a colon and parses multiple values to new columns if necessary. Is that possible? Thank you

[JBeaucaire]

So if I typed 1:3:5 in cell A1, you'd want that split out to 1 in A1, 3 in B1 and 5 in C1? Can you provide specific columns where this needs to be active and exact examples of where the data should parse out to?

[ladytexan]

Yes, that's exactly what I want. The problem is, I can't necessarily say what columns will have the data that needs to be parsed. I know what rows, but not which columns. That's why I wondered if there was a way I could just have every cell checked after data was entered. It would be fine if they just parse to the cells immediately beside them in the row, the way you described in the example. Is that even possible?

[JBeaucaire]

This is most basic form of a Worksheet_Change event you can install into the Sheet module, it will watch for any strings entered with colon as a delimiter and split them out from the cell entered to the right. Be careful, though. Trying to enter 1:3 in Excel often converts that to a Time of 1:03 AM and that one won't split.

Please Login or Register  to view this content.

[ladytexan]

Thanks so much! That's exactly what I needed it to do

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.