FIND & REPLACE LOOPING
 

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

FIND & REPLACE LOOPING
 

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

FIND & REPLACE LOOPING
 

More details please. Specifically, where is this imported data at? In cells?
In a String variable? In a String array?

--
Rick (MVP - Excel)


"Faraz A. Qureshi" wrote in
message ...
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

FIND & REPLACE LOOPING
 

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


"Rick Rothstein" wrote:

More details please. Specifically, where is this imported data at? In cells?
In a String variable? In a String array?

--
Rick (MVP - Excel)


"Faraz A. Qureshi" wrote in
message ...
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

FIND & REPLACE LOOPING
 

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

FIND & REPLACE LOOPING
 

With your data in Col A...Try the below macro....


Sub MyMacro()
Dim lngRow As Long, lngNRow As Long, strData As String
For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
strData = Range("A" & lngRow): intTemp = 0
Do
intTemp = InStr(intTemp + 1, strData, " ", vbTextCompare)
If intTemp < 0 Then strData = _
Left(strData, intTemp) & "|" & Trim(Mid(strData, intTemp))
Loop Until intTemp = 0
strData = WorksheetFunction.Trim(strData)
Range("A" & lngRow) = Replace(strData, " |", "|")
Next
End Sub

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


"Faraz A. Qureshi" wrote:

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

FIND & REPLACE LOOPING
 

All those "spaces" are not spaces, some are ASCII 160 characters. Try this.
Select all the cells you want to process and then run this macro...

Sub FixSpaces()
Dim C As Range, S As String
For Each C In Selection
S = Replace(C.Value, Chr(160), " ")
Do While InStr(S, Space(4))
S = Replace(S, Space(4), Space(3))
Loop
C.Value = Replace(S, Space(3), "|")
Next
End Sub

--
Rick (MVP - Excel)


"Faraz A. Qureshi" wrote in
message ...
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


"Rick Rothstein" wrote:

More details please. Specifically, where is this imported data at? In
cells?
In a String variable? In a String array?

--
Rick (MVP - Excel)


"Faraz A. Qureshi" wrote in
message ...
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