08 Feb 2015

## Video 00121_Crazy String Manipulation!

### 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.

### 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: Get the Excel file here or from my OneDrive  here (file 00121)

### 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.