The data is imported from Text File but the example is just a small sample
and it is actually in a very vague condition however the least space between
two columns is 3 spaces.

Looping structure is what is desired. In other words a code like:
1. Replace " " with "|";
2. Carry on / loop until no other found;
3. Replace " |" or "| " with "|";
4. Carry on / loop until no other found;
5. Replace "||" with "|";
6. Carry on / loop until no other found;
7. Finally, carrying on the exercise of Text To Columns with "|" as the
delimiter?

--
Best Regards,

Faraz


"Jacob Skaria" wrote:

Is that in excel or is it a txt file?

If it is just a one time process; then you can do that with Replace and
DataTexttoColumns...

Ctrl+H
Replace Find what: 3 spaces Replace with |
Replace Find what: 2 spaces Replace with |
Replace Find what: 2 spaces Replace with |

Replace Find what: || spaces Replace with |
Replace Find what: || spaces Replace with |

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

I have an imported data with gaps of unequal size. To convert them I need to
add a special character in this case.

What piece of code could replace a gap of 3 or more spaces with a pipe
character "|".

In other words, data like

Name Region Rate
Name 1 Region x Rate x
Name 2 Region x Rate x
Name 3 Region x Rate x
Name 4 Region x Rate x
Name 5 Region x Rate x

could be converted to:

Name Region Rate
Name 1|Region x|Rate x
Name 2|Region x|Rate x
Name 3|Region x|Rate x
Name 4|Region x|Rate x
Name 5|Region x|Rate x

because only the second row and onwards contain gaps of 3 more spaces and
only a single "|" is inserted.

Thanx in advance.

--
Best Regards,

Faraz