Excel - How To Increment Both Numbers In A Range In Excel - Episode 1969

Microsoft Excel Tutorial: Excel Formula to Increase "1-2" to "3-4" and so on.

Welcome to another episode of the MrExcel podcast, where we dive into all things Excel. In today's episode, we will be discussing a formula to automatically increment numbers in a range. This question was sent in by Haig, who wanted to know how to go from 1-2 to 3-4 and then 5-6, without having to manually enter each number. Well, the solution may seem complicated, but each piece of the formula is actually quite simple.

The first step is to use the FIND command to locate the dash in the first cell. This will help us determine where the second number starts. In smaller numbers, the dash is always in the second position, but in larger numbers, it may be in a different position. So, we use the FIND function to locate the dash and then add 1 to that number to find the starting position of the second number.

Next, we use the MID function to isolate the second number. We choose a large number, like 20, to ensure that we capture all the digits of the second number. Then, we add 1 to that number to determine the starting position of the next cell. Finally, we use the CONCATENATE function to combine the first part of the next cell with the dash and the second part of the next cell.

All of these steps may seem overwhelming, but when you break it down, it's just a matter of building the formula one step at a time. And the end result is a formula that can automatically increment numbers in a range, no matter what the starting number or increment may be. So, whether you want to go from 1-2 to 3-4 or from 7-8 to 9-10-11, this formula has got you covered.

But what if you want to follow a different pattern, like increasing by 3 instead of 2? Well, we've got you covered there too. The second part of the formula allows you to specify the starting number and the increment, and it will automatically generate the rest of the range. So, whether you're starting at 1001 and going to 2000 or starting at 7 and going up by 3, this formula can handle it all.

In conclusion, while this formula may seem complex at first glance, it's really just a combination of simple functions that work together to automatically increment numbers in a range. So, next time you find yourself needing to increment numbers, remember this formula and save yourself some time and effort. Thank you for tuning in to this episode of the MrExcel podcast, and don't forget to send in your Excel questions for a chance to be featured in a future episode. See you next time!

Table of Contents:
(00:00) Increment two numbers in one Excel cell
(00:12) Using the FIND command to locate the dash in A1
(00:27) Using the MID function to isolate the second number
(00:46) Adding one to the second number to determine the starting point for the next cell
(01:09) Concatenating a dash to the first part of the next cell
(01:48) Repeating the process for the second part of the formula
(02:13) Combining all steps into one formula
(02:26) Testing the formula with different starting numbers and increments
(03:04) Exploring the possibility of following a specific pattern
(03:15) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelchallenge #excelformula #excelformulasandfunctions

This video answers these common search terms:
excel how to parse text in cell
how to get excel to parse string
how to increment both numbers in a range in excel
excel how to have two incrementing numbers in one cell
how to parse a value in excel
how to parse data in excel using formula
parse two values and increment in excel
how to make excel increment two numbers
how to add increments in excel

Haig wants a formula that will increment a cell with 1-2 into 3-4 and then 5-6 and so on. While the formula ends up being fairly long, it is a collection of steps to break the original cell apart and increment.

[cjimmer]

Thanks Mr Excel.
Here is another option.

[gregwatw]

Why not just use 2 helper columns starting with 1 in the first column and 2 in the second column of the first row.
Then in the next row add 2 to the cel above. In the third column then use the formula =A1&"-"&B1.

[docrevoooo]

BUT HOW WOULD IT DO IN ROWS. NEED THEM BADLY 😔