Many Formulas in one cell

[jewpal]

Hello knowledgeable users!

I am trying to fix a series of numbers. in E2 it reads:

20180809173450Z

I want it to be:

08092018

This is what I have so far:
=MID(E2,7,2)&LEFT(E2,LEN(E2)-9)
Which returns 09201808.

Ok almost there, just have to do that same thing to put 08 in front of the 092018.

=MID(E2,7,2)&LEFT(E2,LEN(E2)-9)&RIGHT(E2,2)

AHCK it does not work! I get:

<colgroup><col width="283"></colgroup><tbody>
[TD="class: xl65, width: 283"]092018080Z

So it seems that the formula is still using what is in E2 and not the "new result."
Any advice would be greatly appreciated!!
[/TD]

</tbody>

 

[philwojo]

If you data is always in the format, just try this formula:

=MID(E2,5,2)&MID(E2,7,2)&LEFT(E2,LEN(E2)-11)

 

[philwojo]

Or even this, which gives the same result:

=MID(E2,5,4)&LEFT(E2,4)

 

[jewpal]

Thank you! It would be cool if they were all in the same format. I do have some like this:

201818070121Z

That should read 07182018
Just to make it more difficult

 

[philwojo]

That is the same format as your first example, at least for the date portion at the start, so that same formula should work for it as well.

Try it out and if things aren't working post back specifics and we can address from that point.

 

[jewpal]

Hi, it spits out
[TABLE="width: 283"]
<colgroup><col width="283"></colgroup><tbody>[TR]
[TD="class: xl65, width: 283"]18072018
[/TD]
[/TR]
</tbody>[/TABLE]
It is different than the first example because the month and date are reversed

 

[philwojo]

Sorry I got ahead of myself i see that as well, how do you know when the Month is first and the Day second, is there anything to indicate that to you?

 

[jtakw]

Hi,

The problem here is how would the formula know which one is the day and which one is the month between the two different formats?

For instance, 20181209025221Z

Should the result be 09122018 or 12092018 ?

 

[philwojo]

This formula would work for your 2nd example:

=MID(E3,7,2)&MID(E3,5,2)&LEFT(E3,4)

Change the E3 to be whatever cell you need to point it too. If you can provide any guidance as to when you know it is in one format versus the other we might be able to build in some logic for that.

 

[jewpal]

They are actually on two different sheets so they are separated. The first example is definitely working correctly keeping the 4 digits together, putting them in front, and removing the remaining characters.
The second tab is all the reversed date.
Thank you very much philwojo that worked! What a great forum. Have a nice day everyone !!