Excel. Placing Colon.

[alliswell]

Hi everybody
I have some texts and numbers in column A. I want to place ":" sign after first 3 characters and before last two characters. How can i ? I tried with cell format but failed.

 

[jasonb75]

You can't format cells that contain text, only numbers or numeric values.

 

[alansidman]

Book8
	A	B	C
2	Reyes__Ma__LuisaText123		Rey:es__Ma__LuisaText1:23
3	Jun-Oh__JuneeText123		Jun:-Oh__JuneeText1:23
4	Carter__John_Jr_Text123		Car:ter__John_Jr_Text1:23
Sheet3

Cell Formulas
Range	Formula
C2:C4	C2	=LEFT(A2,3) & ":" & MID(A2,4,LEN(A2)-5) & ":" & RIGHT(A2,2)

 

[jasonb75]

@alansidman you don't need to split the string to insert characters with a formula, just replace 0 characters with the text to insert.

=REPLACE(A2,4,0,":")

 

[alliswell]

Thanks everybody. Got It !

 

[alansidman]

@jasonb75

Isn't it great that we all have different ideas pop into our heads at different times so that the OP can have multiple ways to solve their issues.

Alan

 

[jasonb75]

Alan, apologies if my post came across as being facetious, I wasn't sure if you were aware of the method. In all honesty it was something that I tried once, not expecting it to work.

I just realised that I missed the part about adding a second colon at the end of the string, this will correct that.

=REPLACE(REPLACE(A2,LEN(A2)-1,0,":"),4,0,":")

I think that it might get a bit messy with any more than 2 additional strings though.

 

[alliswell]

Thanks Dear Jason. No appologizes. You r gr8. all mrexcel are gr8

 

[alliswell]

Hello alansidman and jason. Thanks for solving my query. can you explain what does -5 and -1 mean ?

 

[alliswell]

Sir i have entered time in A1 by formula =now() and cell format to h:mm. Is there anyway to convert it in text so that the time doesnt update.
Thanks