Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Find/Replace carriage return & line feed characters in Excel.

I have symbols in my Excel 2002 data that represent carriage returns and line
feeds "char(10) & char(13)". I want to find/replace these symbols.


Post your answer or comment

comments powered by Disqus
I have symbols in my Excel 2002 data that represent carriage returns and line
feeds "char(10) & char(13)". I want to find/replace these symbols.

Hey all! I work with really large client data files which I recieve and have to clean up (remove all formatting) before importing into my database software. When there is a carriage return line feed character in a cell, the file is imported all wrong. Needless to say, it gets tedious and maddening to search through a HUGE file looking for these return characters. Is there a short cut that could either trim these characters or at least help me scroll through the file to find them? Please help! Thanks guys.

-eric

Using either the data/text to columns command or substitution functions can anyone advise me as to how to parse the data in the attached spreadsheet into columns using the carriage return line feed (paragraph symbol) as the data column divider.

In the data it looks like a small box or two.

Thanks,

LongFisher

I have some data that I am manipulating in Excel to do an export to another
application. I need to be able to do a universal find and replace to get rid
of instances of Carriage Returns and Line Feeds within the data. I have
tried the basic find and replace functions but I don't know how to make them
find a carriage return character.

Any help would be greatly apreciated.

Hi

Imported Text in cells are formatted with 3 line feed characters. If you
use clean() it deletes the characters and puts the text into one line. I
need to be able to delete the Chr(10)
and keep the formatting by a macro if possible.
This file is needed to be uploaded into SAP which doesn't like CHR(10).

Presently the only way to do this is to go to the end of each line and
manually delete the three CHR(10) and all works well as
the formatting stays the same.
I have tried Substitute and Find and replace, Clean() but it always ends up
in single
line....
Hope someone can help....
Thanks
Rob

I am using Microsoft Query to retrieve data from a csv file. The csv file contains a mixture of data types, some text and some values.

Some of the fields contain Carriage Returns or Line Feeds (CR/LF) not sure which? embedded in the fields displaying as square boxes i.e. "MBRB123A12345□"

When I create a query I seem to be getting a problem with query interpreting these CR/LF and the query not returning the data correctly (the character is being treated as a end of record mark and fields after the CR/LF are being ignored.

Does anyone know how to stop MS Query interpretting these CR/LF as end of record marks, if not, does anybody know how I can strip them out of the source file

Is there anyway to use Find/Replace to delete all the carriage returns in a column of cells? Thanks in advance!

I have a need to be able to create a funtion/expression that can issue a
'hard carriage return & line feed'. On the keyboard this would be 'Alt' +
'Enter'. I want to take two (or more) cells and combine them into one cell
with each value on it's own 'line' in the receiving cell.
============================
Receiving Cell Cell 1 Cell 2
Smith John Smith
John
============================
I am trying to create a Membership Directory for print and distribution. I
do having it working by determining the length of the Sending Cell and then
padding the end with 'n' spaces depending on the size of the Receiving Cell
and the length of the Sending Cell. This works but is rather arbitrary
unless using fixed fonts which is not reasonable.
Could I capture this 'special' character by recording a macro (which I will
try)? Or does someone know what this 'special' character might be?

Thanks for any help you might provide,
DannyDont

I have a need to be able to create a funtion/expression that can issue a
'hard carriage return & line feed'. On the keyboard this would be 'Alt' +
'Enter'. I want to take two (or more) cells and combine them into one cell
with each value on it's own 'line' in the receiving cell.
============================
Receiving Cell Cell 1 Cell 2
Smith John Smith
John
============================
I am trying to create a Membership Directory for print and distribution. I
do having it working by determining the length of the Sending Cell and then
padding the end with 'n' spaces depending on the size of the Receiving Cell
and the length of the Sending Cell. This works but is rather arbitrary
unless using fixed fonts which is not reasonable.
Could I capture this 'special' character by recording a macro (which I will
try)? Or does someone know what this 'special' character might be?

Thanks for any help you might provide,
DannyDont

Hope someone can help me out with these 2 issues:

(1st Problem)

I have a spreadsheet that in one section has Part Numbers , Assembly
ID, Q/A Code, Quantity, Production Tag # ...... and so on ad nauseam.

A1:Q34

Right now I manually insert a new line for each new part number and
this is tedious when a project might have 200 lines to be entered and
error checked.

Is there not a way using vba to do a carriage return/line feed instead
of the good but mind numbing manual way.

--------------------------------------------------------------------------------------------------------

( 2nd Problem )

The same spreadsheet is sent to other people and signed but some areas
I have protected so they can be viewed but not inadvertently altered.
What I would like to do is set the scroll area automatically so people
can tab from one place to the other and enter their names and comments
as required.

As the spreadsheet varies in depth according to the number of parts
entered I don't seem to be able to set the scroll area to increase or
decrease accordingly.

Thank you in advance for any pointers you might toss my way.

--
ZimBoy
------------------------------------------------------------------------
ZimBoy's Profile: http://www.excelforum.com/member.php...o&userid=24273
View this thread: http://www.excelforum.com/showthread...hreadid=378906

Hello,

The online help for Microsoft Excel 2003 says to disable wrap-text in
order not to have wrapping of long lines within cells. However, doing
this also turns line feeds into non-line-feed characters (some kind of
strange symbol serving as a place-holder for the linefeed). I only
want to disable wrapping of long lines, I don't want to remove the
effect of hard coded line feeds. How is this done in Excel? (If it
can't be done, that would also be helpful to know). Thanks.

I need some help in replacing carriage returns that is occuring in the excel file using vbscript.

Anyone to guide me?

Thanks

We are using Excel to load data into SQL.

We are using the Concatenate function to join a couple columns of data and
we want to put a CRLF (Carriage Return, Line Feed) after the first column
(line) of data.

=IF(B2=F2,A2,CONCATENATE(A2, CHAR(10), B2) )

This data will be loaded into a nvarchar field (notes) in SQL. I know about
using the 'wrap text' formatting to get rid of the square box display in
Excel and display the text as two lines. However, when we import this data
into the SQL database, the square box is what is displayed, and the data is
not being put on two separate lines.

Anyone ever encountered this problem and have any solutions?

Thanks,
Leslie Fournier
Oregon Aero, Inc.

Hello,

I would like to format the columns of my excel spreadsheet to have carriage
returns
after the contents of each cell.

I am trying to import this information into an AutoCad mText box, and if the
excel cells do not have carriage returns, after I copy and paste the excel
data into the mText box, I have to manually insert carriage returns.

Any suggestions?

Thank You,
Laurel Hunter

I was looking to find strings made up of wildcard characters in Excel, and
I've been having some problems.

For example, I was searching for the string "*?" in my spreadsheet (without
the quotes), and it was directing me to every cell with data in it. If I
search for "{*" (without the quotes), it directs me to every cell with data
that begins with the "{" amperscand character.

Does anyone have a similar problem, or know how to get around this? Thank
you in advance.

MATCH function may return an unexpected value in Excel

I'm not sure where to post this, but, my question is with private characters in excel.

I wanted somthing like in wingdings characters representing ones, twos, fives and tens for hundreds. Using private character editor I created what I needed, but, when applied, my workbook came to a crawl each time it would recalculate or refresh the screen. I know I followed the steps right. I'm wondering if there is another to accomplish what I want without loosing performance?

Thanks

Need help - if my header / footer contains special character "&" in excel -
the result come out incorrect.
Is there any way to put this special character as part of my header / footer
in excel ?

How do I display white space characters in excel?

Can anyone please let me know "How do I measure area under a line chart
drawn in Excel file?"

I have a macro in excel that is copying out to another newly created workbook then saving that workbook as a tab delimited (.txt) file.

The issue that I am having is that I am trying to upload this file into another application (MS Dynamics/Great Plains) and it has a large number of characters trailing at the end of the data.

For Example say I have the following in excel in columns and a row

Column1 Column2 Column3
Fred Smith RI
George Jones CA
Carl White VA

When I copy this out I expect that the last character would be the letter A in VA in Column 3. However what I end up with is 40 or so characters after it. Now in the excel worksheet I do have formulas that carry down below the total text in order to ensure that the formula is there for any possible number of rows. Is this the issue?

So I guess my question is: A how do I eliminate these "blank" characters in excel OR how do I do so if the file is in notepad (can I apply an Excel Macro to notepad)

Or, if its my extra formulas; is there a way to copy a formula down only so far as the last row of data. So for example in the data above I have the data displayed in Column 3 is actually from a vlookup that carries down 50 rows (it is told to be "" if column 1 is empty) in case I have 50 rows of data. I could have the formula only in one cell and copy it down just only so far as needed.

Hopefully this makes sense.

Here is the relevant part of the code I am using:


	VB:
	
 EntrySetup() 
     
     
     
     
    Application.ScreenUpdating = False 
     
    Calculate 
     
     
    Sheets("Entry Prep").Select 
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 
     
    Calculate 
     
    Sheets("Entry Prep").Select 
    Range("E5:E1000").Select 
    Selection.Copy 
    Sheets("LoanSalesEntry").Select 
    Range("G2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
    Sheets("Entry Prep").Select 
    Application.CutCopyMode = False 
    Range("F5:F1000").Select 
    Selection.Copy 
    Sheets("LoanSalesEntry").Select 
    Range("D2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
    Sheets("Entry Prep").Select 
    Application.CutCopyMode = False 
    Range("H5:I5000").Select 
    Selection.Copy 
    Sheets("LoanSalesEntry").Select 
    Range("E2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
    Sheets("Entry Prep").Select 
    Application.CutCopyMode = False 
    Range("J5:J5000").Select 
    Selection.Copy 
    Sheets("LoanSalesEntry").Select 
    Range("B2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
    Range("A1").Select 
     
    Calculate 
     
     
     
    Sheets("LoanSalesEntry").Select 
    Sheets("LoanSalesEntry").Copy 
    ActiveWorkbook.SaveAs Filename:= _ 
    "T:AccountingDynamicsUploadEntryLoanSalesEntry.txt", FileFormat:=xlText _ 
    , CreateBackup:=False 
     
    ActiveWorkbook.Close False 
     
     
     
     
End Sub[COLOR=#3E3E3E][B][/B][/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Need help - if my header / footer contains special character "&" in excel -
the result come out incorrect.
Is there any way to put this special character as part of my header / footer
in excel ?

Can anyone please let me know "How do I measure area under a line chart
drawn in Excel file?"

Hi all,

I need to find all <CR> character (carriage return) because when I export a table from Access to Excel, the data contains "[]" that are carriage returns.

so i need to find these characters and replace with null

can i do this with vba?

many thanks


No luck finding an answer? You could always try Google.