View Full Version : Solved: Replacing Special Characters & Symbols
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
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
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?
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.
Hmmm, that script does not appear to be doing anything.
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.
I've attached another sheet with a bit more explanation. I hope it helps.
Thanks,
Opv
Disregard. Your updated script is working now. I must have done something wrong before. Thanks again for your help.
Opv
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.
Thanks for going to so much trouble. I appreciate the help.
Opv
Thanks for going to so much trouble. I appreciate the help.
Opv
Happy to help, Opv!
Cheers :beerchug:
Vladimir
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.