Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing Carriage Returns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing Carriage Returns
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...col.htm#dbdata --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "David" wrote in message ... 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 [separated] with Carriage Returns. Is there any way I can replace this with a characted such as % or | (I know how I can seperate [separate] it into different columns if I can replace it with a unique character) Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing carriage returns with a comma | Excel Discussion (Misc queries) | |||
Carriage returns - how to get rid of them (again) | Excel Discussion (Misc queries) | |||
Delete Carriage Returns | Excel Discussion (Misc queries) | |||
Replacing commas with carriage return | Excel Worksheet Functions | |||
Carriage returns | Excel Discussion (Misc queries) |