Replacing Carriage Returns

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hi,
I have a spreadsheet that is populated with Name and
Address data from a SQL Server database using MS Query.
Unfortunately the database stores the address in one
column, with the different parts (ie street, town/suburb)
seperated with Carriage Returns. Is there any way I can
replace this with a characted such as % or | (I know how I
can seperate it into different columns if I can replace it
with a unique character)

Thanks
 
anonymous poster David,

09 TAB
10 LF (line feed) hex 0A
13 CR (carriage return) hex 0D

You can replace CR with TAB and then you will be set
using the default delimiter in Text to Columns

in VBA
expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte)
-- usually written something like---

Selection.Replace What:=CHR(13), Replacement:=CHR(09)

if you want another character you can use Replacement:="%"

Since you are using VBA it might make more sense to simply
do everything in VBA

Sub SepColumnOnLF()
'-- if you have multiple LF in cells insert more columns and loop the LF
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'-- insert column to right of active cell, before separations
Dim cell As Range, i As Long
ActiveCell.Offset(0, 1).EntireColumn.Insert
For Each cell In Intersect(ActiveCell.EntireColumn, _
Selection.SpecialCells(xlConstants, xlTextValues))
i = InStr(1, cell.Value, Chr(10))
If i <> 0 Then
cell.Offset(0, 1).Value = Mid(cell.Value, i + 1)
cell.Value = Left(cell.Value, i - 1)
End If
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


In Excel
Make selection
Ctrl+H
from: alt+0013 alt+0010 (two characters) on the numeric keypad, or
from alt+0010 (one character) on the numeric keypad
to: alt+0009 on the numeric keypad

You may have CRLF, CR, or LF depending on software, I don't know which you
will have. Excel generates a LF when you use Alt+Enter for instance.

May be of interest:
Creating a Spreadsheet from Database data (#dbdata)
http://www.mvps.org/dmcritchie/excel/snakecol.htm#dbdata
 
Back
Top