PDA

View Full Version : Solved: Replacing Special Characters & Symbols



Opv
03-04-2010, 03:48 PM
I recently exported my Outlook contacts to a CSV file and imported the data into EXCEL. Several of the cells include a symbol which looks like an empty square box. (See attached, target cell highlighted in yellow.)

Is there any way I can strip out the square boxes and separate the lines from the one cell into three cells?

Thanks,

Opv

ZVI
03-04-2010, 04:48 PM
Hi Opv,

Square boxes are concatenation of 2 symbols, i.e. carriage return CHR(13) and line feed CHR(10).
The last char in D2 is the single line feed CHR(10).
This macro provides the convertion of selection to columns.
Select D2 cell or several cells and run the macro:


Sub SelectionToColumns()
Dim Arr, x
On Error Resume Next
For Each x In Selection
If InStr(x, vbCr) Then
Arr = Split(Replace(x, vbCr, ""), vbLf)
If Err = 0 Then x.Resize(, UBound(Arr) + 1).Value = Arr
Err.Clear
End If
Next
End Sub
Regards,
Vladimir

Opv
03-04-2010, 05:05 PM
Thanks. That almost does the trick. I didn't notice this earlier, but some of my cells only have the square box at the end of all but the last line. The current script is splitting out only the lines with the square box and stripping out the last line that does not have the square box at the end. Is there a way to retain the last line in those cases and split those lines into their own cells?

ZVI
03-04-2010, 05:26 PM
I've applied modification into previous code - please try it updated in post #2.

And this code is just for deleting of vbCr:


Sub StripCr()
Dim x As Range
For Each x In Selection
If InStr(x, vbCr) Then
x.Value = Replace(x.Value, vbCr, "")
x.WrapText = True
End If
Next
End Sub

Is this you are after? - If not then please attach the example.

Opv
03-04-2010, 05:33 PM
Hmmm, that script does not appear to be doing anything.

ZVI
03-04-2010, 05:40 PM
Hmmm, that script does not appear to be doing anything.Script of post #4 just replaces all square boxes in selected cells by the normal (invisible) line feeds :)
Please show what you have in the cell(s) and what you want in result.

Opv
03-04-2010, 06:19 PM
I've attached another sheet with a bit more explanation. I hope it helps.

Thanks,

Opv

Opv
03-04-2010, 06:25 PM
Disregard. Your updated script is working now. I must have done something wrong before. Thanks again for your help.

Opv

ZVI
03-04-2010, 07:15 PM
This version of the code insertes the converted content and shifts existing data (columns E,F,...) of each row to the right side:


Sub SelectionToColumns()
Dim x As Range, Rng As Range, i As Long, Arr
Application.ScreenUpdating = False
On Error Resume Next
For Each x In Selection
If InStr(x, vbCr) Then
Arr = Split(Replace(Replace(x, vbCr, ""), vbLf & vbLf, vbLf), vbLf)
If Err = 0 Then
i = UBound(Arr) + 1
Debug.Print i
If i > 1 Then
With x.Offset(, 1).Resize(, i - 1)
.Copy
.Insert Shift:=xlShiftToRight
End With
End If
x.Resize(, i).Value = Arr
End If
Err.Clear
End If
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

If the result is not exact as required then please type into the cells manually the required result.

Opv
03-04-2010, 07:18 PM
Thanks for going to so much trouble. I appreciate the help.

Opv

ZVI
03-04-2010, 07:36 PM
Thanks for going to so much trouble. I appreciate the help.

Opv
Happy to help, Opv!
Cheers :beerchug:
Vladimir