FIND & REPLACE LOOPING

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

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.
 
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
 
More details please. Specifically, where is this imported data at? In cells?
In a String variable? In a String array?
 
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 said:
More details please. Specifically, where is this imported data at? In cells?
In a String variable? In a String array?
Click to expand...
 
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?
 
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
 
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 said:
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?
Click to expand...
 
Back
Top