Formatting literally text

"I want the value ... to be ..."

Custom formatting will NOT change the value, only the format, ie the way it appears on screen.

Indeed you're correct Special-K. My wrong choice of words.

Let me rephrase:

I want the 1.3.13 to appear as 1.03.013; so add a 0 to the place I want in the text.
Can I make a custom Format Cells "formula", or i.e. some formatting with TEXT to achieve this?

Thanks and best regards,

Meibrah

I think I would do a "text to columns" ("." as the delimiter) to separate the three numbers into separate cells. Then a simple =CONCATENATE() and TEXT() combination should be able to recombine the text string. Something like =CONCATENATE(A2,".",TEXT(B2,"00"),".",text(C2,"000")).

I'm not sure what you mean by "a direct way". IMO, using the Text to Colummns command to separate the text string into its parts, then rejoining with CONCATENATE() is a pretty direct way to perform the task.

Perhaps by "direct way" you prefer something that uses only excel's worksheet functions. I am not proficient with Excel's text manipulation functions (https://support.office.com/en-us/art...__toc309306717 there should be a similar help file in your Excel installation, especially if you prefer to use the help file in a language other than English), but I expect it would look something like:

1) Use the FIND() function to locate the position of the "." characters in the original text string.
2) Use the LEFT(), MID(), and/or RIGHT() functions to extract the parts of the text string.
3) The VALUE() function may be needed to convert "numbers as text" to numbers before inputting into the TEXT() function.
4) Then, use the CONCATENATE() and TEXT() functions as I described above to recombine the text strings as desired.

In essence, we are replacing the single Text to columns command with a combination of worksheet functions.

And, this becomes more difficult if you insist on performing all of those operations in a single cell mega formula, which I never do.

In terms of "divide and conquer" which parts of this do you need help with?

The TEXT Function is for converting numeric values to text with formatting so it cannot be used directly on your text strings. You would need to convert them to numbers first as Mr Shorty proposed in post #4

Might this be something that does what you want? It is on the edge of mega-formula.

Row\Col	A	B	C
2	1.09.13	1.09.013	In b2 =TEXT(LEFT(A2,1)/1440+(MID(A2,3,2)/86400)+RIGHT(SUBSTITUTE(A2,".",REPT(" ",20),2),10)/86400000,"m.ss.000")
3	4.2.1	4.02.001
4	4.3.448	4.03.448

This manipulates the string and formats it as "time".

Edit I probably ought to try and explain this one. It coerces the string into "pretending" to be a time value as MrShorty and ChemistB suggested above. If you are not aware of it time is a numeric value ..... a decimal fraction of a day. This formula parses each section. The first is coerced into minute fractions of a day ... hence the divide by 1440 (1440 minutes in a day). The next section is coerced into second fractions of a day or multiples of 1/86400 of a day. The next is multiples of one-thousandth of seconds. Summed up they equal the decimal fraction of a day which lends itself to TEXT with a time formatting (substituting conventional ":" separations with ".").

It's an admittedly rather "cheesy" way to do it. It was the most compact single formula approach I could come up with, and it seems to do what you want.

Another edit BTW if there are numbers larger than 60 in the middle section this isn't going to work.