Inserting two periods into string of Text

[JCasco]

Hey guys I'm having a hard time figuring out how to add two periods into a string of text.
the column looks like this: A0642000403 and I'm trying to add two periods to format it like this: A0642.0004.03
All strings of text are the same length and need the periods in the same place.
I can add one period using substitute code but having a hard time with two of those playing nice, I'm guessing because it adds the period in there and might be messing with the second portion of the substitute code.

Any Suggestions?

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	A	B
1	A0642000403	A0642.0004.03
2	A0642000404	A0642.0004.04
Sheet1008

Cell Formulas
Range	Formula
B1:B2	B1	=REPLACE(REPLACE(A1,6,0,"."),11,0,".")

 

[Micron]

using substitute code

As "code" (vba expression), Left("A0642000403",5) & "." & Mid("A0642000403",6,4) & "." & Right("A0642000403",2) where you replace the value with a reference to the value.
EDIT
If you mean 'formula' then you have that too.

 

[JCasco]

=REPLACE(REPLACE(A1,6,0,"."),11,0,".")

Worked perfect, thank yo so much! I didn't realize you could just add on that second portion without having to add & in the code!

 

[JCasco]

As "code" (vba expression), Left("A0642000403",5) & "." & Mid("A0642000403",6,4) & "." & Right("A0642000403",2) where you replace the value with a reference to the value.
EDIT
If you mean 'formula' then you have that too.

Thank you so much!

 

[jtakw]

Worked perfect, thank yo so much! I didn't realize you could just add on that second portion without having to add & in the code!

You're welcome, thanks for the feedback.

 

[Micron]

Seems like the more I hang around here, the more I see functions in Office apps with exactly the same name yet different requirements. This is why I'd probably never think of that one on my own (at least before now):
Access - Replace(expression, find, replace [, start ] [, count ] [, compare ] )

 

[Rick Rothstein]

As "code" (vba expression), Left("A0642000403",5) & "." & Mid("A0642000403",6,4) & "." & Right("A0642000403",2) where you replace the value with a reference to the value.

I know the OP ended up wanting a formula, but I thought you would find it interesting that (If the text values are always 11 characters long) the VBA expression can be much more compact...

Format("A0642000403", "@@@@@.@@@@.@@")

 

[Micron]

All this new stuff I'm learning - and no need for it really. Gone are the days when I might have been called back to work for Access & Excel programming. Keeps the old noodle from wilting, I guess (I meant my brain, by the way).