FIND & REPLACE LOOPING

[Faraz A. Qureshi]

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.

 

[Jacob Skaria]

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
Data>TexttoColumns...

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

 

[Rick Rothstein]

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

 

[Faraz A. Qureshi]

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

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

 

[Faraz A. Qureshi]

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?

 

[Jacob Skaria]

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

 

[Rick Rothstein]

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)

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?