08 Feb 2015

Look carefully at the picture below for a few seconds…

Crazy String Manipulation

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:

New DateTime Values_08-02-2015 6-39-09 PM

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.

Text Values Extracted_08-02-2015 7-26-36 PM

 

 

 

 

 

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.

variables_08-02-2015 7-05-18 PM

 

Step 5   CREATE DATES

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

New Dates_08-02-2015 7-06-51 PM

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!

Save

Post a comment