- BY Kevin Lehrbass
- POSTED IN Solutions
- WITH 0 COMMENTS
- PERMALINK
- STANDARD POST TYPE

**Look carefully at the picture below for a few seconds…**

If you extract all numbers, from left to right, you can create a Date/Time value of **Nov 28, 2014 5:08pm** (and 51 seconds) in Microsoft Excel.

### Here is our task……

**Create a formula to add x amount of days, hours and minutes while keeping all of the text in the same order!**

If we decide to add 3 days and 2 hours to each cell then we should see this:

**How do we do this? **Watch my video, download the file or read the steps below.

**Watch my YouTube video**

**Download my Excel file**

Get the Excel file **here** or from my OneDrive **here**** **(file 00121)

**Follow These Steps**

**Step 1 EXTRACT DATE & TIME NUMBER POSITIONS**

As the amount of text between the numbers can vary, we need to determine exactly where our date and time ‘pieces’ are found. We can do this using Excel’s SEARCH function.

**{=MIN(IFERROR(SEARCH(Digits,C$10),””))}** The SEARCH function uses a named range called ‘Digits’, which is an array constant ={0,1,2,3,4,5,6,7,8,9}, and gives us the position number of all of the digits found within the cell. We care about the smallest number. The ‘2’ is found is position number 6 in our text string seen here: “**dfZSa2**“. Note that the second formula looks like this: **=MIN(IFERROR(SEARCH(Digits,C$10,C13+4),””)) ** as we aren’t looking from the beginning of the cell. We are looking for the next number after the first number.** **

**Step 2 EXTRACT TEXT VALUES **

This step is a bit easier. We use the LEFT function **=LEFT(C$10,C13-1) **and then the MID function **=MID(C$10,C13+4,C14-C13-4) **for the rest of them.

**Step 3 BUILD ORIGINAL DATE TIME VALUE**

Using the position numbers of the date & time values in Step 1, we can then add **=DATE(MID(C$10,C13,4),MID(C$10,C14,2),MID(C$10,C15,2))** and **=TIME(MID(C$10,C16,2),MID(C$10,C17,2),MID(C$10,C18,2)) **to create a real date / time value.

**Step 4 INPUT VARABILES**

Now, simply input the number of days, hour and minutes that you want to add. In this case we are adding 3 days and 2 hours.

**Step 5 CREATE DATES**

Below, we have our new list of dates that increase by 3 days and 2 hours each.

**Step 6 FINAL NEW TEXT STRINGS!**

**FINALLY THE LAST STEP !** Now, we just have to concatenate the text values with the date value components. The formula looks like this:

**=C$21&YEAR(C42)&C$22&MONTH(C42)&C$23&DAY(C42)&C$24&HOUR(C42)&C$25&MINUTE(C42)&C$26&SECOND(C42)**

Luckily, not all string manipulation requirements are like this but it’s incredible what you can do if you can see the pattern and break it down into a few steps.

**Subscribe to my YouTube channel and learn more!**

**See my free templates here!**