Split Text to Columns or Rows in Excel

Did you ever wonder if there was an Excel formula that could split a text into columns or rows? Well, there is now!

Introducing Split Text XL, which provides the SPLITTEXT formula. Use the SPLITTEXT formula to split text to columns or rows by a specified delimiter. The formula works similar to the “Text to Columns” feature in Excel, but also offers the option to split text to rows.

Use Split Text XL to split full names into first and last names, or use it to format CSV data as needed.

Download a free trial of Split Text XL at http://TumblingWave.com today!

Lines Numbers in Office Macros

This is an article to demonstrate how to show line numbers in your Office macros.

Why would you want to show line numbers in your Office VBA code?

  • Most importantly, line numbers help in debugging your code.

By default, it’s pretty difficult to find the location of an error in an Office macro because Office doesn’t keep track of the line number that has caused the error. Interestingly enough, Office provides us with a VBA function called “Erl” that is supposed to indicate the line where the error has occurred, but that function always returns zero (0) by default. Why? Because the “Erl” function requires line numbers to be present at the start of every line in your code. Argh!

So, what can you do to show line numbers? The most obvious solution is to manually type-in the line numbers. If manually typing hundreds of line numbers sounds tedious, that’s because it is, as experienced by the gentleman on the right.

A better solution would be to use a third-party tool to automatically show line numbers for you. One such tool is called:

VBA Line Numbers with CodeLiner

This tool, made by Tumbling Wave Software, will show (and hide) line numbers in your Excel, Access and Word files. It installs a toolbar in the VBA Editor, so you can just toggle the line numbers on and off. Once you have the line numbers showing, you can actually use the “Erl” function as it was intended – to find the line number of your error!

If you want to find out more about VBA Line Numbers with CodeLiner, you can download a copy on the Tumbling Wave Software website. I wish everyone happy coding!

Version 6.0 of CodeLiner

The newest version of CodeLiner has an option to customize your starting line number!

CodeLiner shows line numbers in your VBA macros.

Why are line numbers so important? Well, did you ever notice that the Erl function always returns 0 in your Office code? That’s because Erl requires line numbers in order to work properly. Once you have line numbers, you can use Erl to automatically show the exact location of your error, making debugging a lot easier.

Go to the Tumbling Wave Software product page for a free trial download, and improve your code with line numbers!

Certified by AppVisor and ASP

AppVisor and the Association of Software Professionals (ASP) are two groups synonymous with trusted downloads of third-party vendor software.

As a vendor working with these two groups, Tumbling Wave Software is pleased to announce that its latest product, VBA Line Numbers with CodeLiner, has been fully certified by AppVisor and ASP!

For a free trial of VBA Line Numbers with CodeLiner, go to the Tumbling Wave Software product page, or check out other products offered by Tumbling Wave Software!

Joining Cells in Excel

This article discusses options when trying to join together cell values in Excel.

Many times in Excel you may want to combine several cells into one cell. For example, if your sheet has dates scattered across different cells, you may want to bring them all together into one comma-delimited list of dates. Excel has several formulas that can do this, and here they are:

Excel FormulaHow Good Is It?
CONCAT
TEXTJOIN
CONCATENATERANGE

Let’s discuss each of these formulas one by one. The CONCAT formula replaces the old CONCATENATE formula. CONCAT improves upon CONCATENATE because you can use it to join a range of cells rather than just cells. For example, CONCAT(A1:A3) brings together all cells between A1 and A3. However, one drawback of CONCAT is that there is no way to specify a delimiter, such as a comma (,), to separate each value within the result. Another drawback of CONCAT is that the values will not be formatted, which can be critical if you are working with dates, for example.

The TEXTJOIN formula is better than CONCAT, because you have the option to specify a delimiter. So, TEXTJOIN(“,”, A1:A3) results in a comma-delimited list of the values drawn from A1 thru A3. There is a problem with TEXTJOIN however – the cells being joined will not be formatted in the result.

That brings us to the ultimate solution – the CONCATENATERANGE formula. The CONCATENATERANGE formula does everything the TEXTJOIN and CONCAT formulas do, in addition to formatting the result using the individual cell formats within the range. The other big plus of CONCATENATERANGE is that it works with ALL versions of Excel.

If you’d like to use the CONCATENATERANGE formula, you’ll need to download it from its creator, Tumbling Wave Software. Here is a direct link to download the CONCATENATERANGE formula.

So, I hope you’ve found this article useful. To all my Excel friends:

Excel Date Time Duration

This article explains how to calculate date/time differences between cells, including milliseconds.

I had once received a large amount of log data in an Excel file, and was charged with having to find the duration between some of those logged events. There were thousands of events, time-stamped with values that even included milliseconds! As an example, one of the time stamps looked like this:

Jun/30/2020 09:10:50.500

This indicates a time value which includes 500 milliseconds (or, in other words, a half-second). First of all, it is interesting to note that to display milliseconds in a cell, you have to format the cell with a “.000” at the end of the format. Sticking with the above example, the cell format would be:

mmm/dd/yyyy hh:nn:ss.000

Now, just because we can display milliseconds, doesn’t mean milliseconds is fully supported. In my case, I needed to find the difference between two time-stamps to the nearest millisecond. I was out of luck. None of the Excel formulas I checked supported time calculations involving milliseconds.

The other problem I had was just a quick way to calculate date/time summary statistics over selected ranges of cells. Say, if I selected an entire column of time stamps, I wanted to immediately see the max, min and duration of what I have selected. It seemed like something too tedious to program.

Photo of calendar and clock.

Luckily, there is a little tool you can install that will solve all of these date/time issues. For me, it was these two issues:

  • Time calculations including milliseconds.
  • Date/time summary statistics like earliest, latest and duration of time for whatever cells I selected.

The name of this little tool is:

Excel Date Time Duration

from Tumbling Wave Software. With this little tool, you get a new Excel formula. The name of the formula is:

DATETIMEDURATION

Using the DATETIMEDURATION formula, I was able to calculate the time difference between two cells, even if they involve milliseconds! It is all the date/time functions I ever needed, rolled into one formula.

In addition, the Excel Date Time Duration tool transforms the standard Excel status bar (along the bottom) into a display of date/time statistics. A great feature for me, because the summary changed every time I changed my cell selection, without having to use formulas. The summary includes time duration at all levels: years, months,…, milliseconds. Amazing!

As I step back, I realize there’s a lot of info in this post. I guess the one takeaway is that the Excel Date Time Duration tool will solve a lot of date/time issues, so it’s worth a try if you want to download it from the Tumbling Wave Software website. Have a pleasant evening (or “Have a pleasant millisecond”, as the case may be)!

Embed from Getty Images