How do I insert a character in middle of cell data?

[DMAinCA]

I need to insert a comma into a column of cells. What is the best way to do this?

Example:
Currently: 1296 Smitty's Snail Shop
Needs to be: 1296, Smitty's Snail Shop

Although the comma needs to be inserted in the same place in each cell (after the four digit #) not all of the cells contain the same character length, i.e. the next one says, "1298 Suzanne's Serpent Store."

I've found many ways of doing this when all of the cells have the same character length, but have been unable to find anything that applies to varied lengths.

Thanks in advance for any help!

 

[Scott Huish]

=replace(a1,5,0,",")

 

[Rick Rothstein]

This is Scott's idea, but set up in macro form in order to change the original text within their cells rather than output the change to a new column...

Sub InsertCommasAtPosition5()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(LEN(@),REPLACE(@,5,0,"",""),"""")", "@", Addr))
End Sub

 

[DMAinCA]

I tried this formula before posting to the forum and it didn't work. I see now that my original Excel spreadsheet may be corrupt as I copy and pasted my original data into a completely new workbook, tried the formula again there and it worked fine. Thanks so much!

 

[DMAinCA]

=replace(a1,5,0,",")

I tried this formula before posting to the forum and it didn't work. I see now that my original Excel spreadsheet may be corrupt as I copy and pasted my original data into a completely new workbook, tried the formula again there and it worked fine. Thanks so much!​

 

[DMAinCA]

This is Scott's idea, but set up in macro form in order to change the original text within their cells rather than output the change to a new column...

Sub InsertCommasAtPosition5()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(LEN(@),REPLACE(@,5,0,"",""),"""")", "@", Addr))
End Sub

Thank you!