Insert characters into string in different locations

string excel text worksheet-function vba
user3882752 picture user3882752 · Feb 20, 2015 · Viewed 27.5k times · Source

I have a string which looks like this 20141007023617. And I would like to make it look like this 2014-10-07T02:36:17Z. The T and Z are very important for me. So I would like to insert them as well.

So a recap,

A1 holds this string 20141007023617

B1 should hold this string 2014-10-07T02:36:17Z

Answer

BrakNicku picture BrakNicku · Feb 20, 2015

Solution with TEXT function:

=TEXT(A1,"0000-00-00\T00\:00\:00\Z")

Related questions

How to turn a string formula into a "real" formula

I have 0,4*A1 in a cell (as a string). How can convert this "string formula" into a real formula and calculate its value, in another cell?

Read More
Substring in excel

I have a set of data that shown below on excel. R/V(208,0,32) YR/V(255,156,0) Y/V(255,217,0) R/S(184,28,16) YR/S(216,128,0) Y/S(209,171,0) R/B(255,88,80) YR/B(255,168,40) Y/B(255,216,40) And I want to separate the data in each cell look …

Read More
How do I get the last character of a string using an Excel function?

How do I get the last character of a string using an Excel function?

Read More