Insert colon in text in cell

P

Phoenix

didnt't know what to search to find it on the forum, so here is the
problem.


I need to insert colon to places to the left, like "X12345"="X123:45",

and "X1234"="X12:34" etc.

I'm sure it's a simple answer for you guys, but not for me :confused:
 
M

Max

One way, assuming source data in A1 down
put in B1: =LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)
Copy B1 down

Freeze col B with an in-place: Copy > Paste special > Check "Values" > OK
Then delete col A
 
K

Ken Johnson

Hi Phoenix,
If A1 contains X12345 then =LEFT(A1,LEN(A1)-2) & ":" & RIGHT(A1,2)
pasted into any other cell will result in that cell displaying X123:45

Ken Johnson
 
Top