VBA function: Insert character every X special character

Hi Forum,

I've searched the web for help but without success. I'm searching for a VBA function that inserts a "space + a period ( .)" every 3rd period (.).

The string can vary in lenght and therefore also the occurrence of periods. So the solution has to fit a dynamic string.

As an example the text could be as follows:

1. .xxx.xxx.xx.xx.xxxx.xxxx.xx.xxx.xxx.xxxx.
2. .xxxx.xx.xxxx.xxxx.xxx.xxx.xx.

Where i want the result as:

1. .xxx.xxx. .xx.xx. .xxxx.xxxx. .xx.xxx. .xxx.xxxx.
2. .xxxx.xx. .xxxx.xxxx. .xxx.xxx. .xx.

How can I achieve that?

If the strings are always the same length, then this formula would do it (I know it's not VBA!):

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,".",". .",3),".",". .",6),".",". .",9),".",". .",12)

You may need to replace commas with semi-colons for your locale.

AliGW on MS365 Insider (Windows) 64 bit

	F
2	.xxx.xxx.xx.xx.xxxx.xxxx.xx.xxx.xxx.xxxx.
3	.xxx.xxx. .xx.xx. .xxxx.xxxx. .xx.xxx. .xxx.xxxx.

Sheet: CSVUserList

Hi Ali,


Sorry I was a little fuzzy in the first post. The strings can vary in length and the occurrence of periods can therefore also vary.

I've previous tried with the SUBSTITUTE function as you presented and another solution with Excel function, but none is doing the job if the string varies in lenth.

I'll add the info to my initial post.

I suggest you provide a sample workbook with at least 10-15 worked examples shpowing the variety you have just disclosed.