Free Microsoft Excel 2013 Quick Reference

Carriage return character in field Results

Hello all. I have a Userform with a text field for user data input. I've enabled EnterKeyBehavior to ease user input formating (make a new line if they want).
Problem is when they are done the field where the data is written to on the main sheet now contains squares wherever they pressed the enter key.

Any way of eliminating this?

Thanks a lot.

I have an Excel file that includes text with carriage returns inside the field. I personally achieve this by typing . I received a client's file that is full of text which I need to import into MS SQL. The text once in SQL will be displayed in an HTML page.

The Problem:
I need to replace the carriage returns inside the cells to
. The replace function in Excel cannot recognize the carriage return.

I tried entering the paragraph mark ¶ into the Replace dialog by typing on my keypad. Although the character was entered, Excel could not find that character. I can't find the alt code for the line feed.

I am trying to import a text file in which the user has entered carriage
returns for a field. When I import it into Excel, Excel is considering the
carriage return as a row delimiter. I am creating this text file from a .NET
program and would like that program to have to enter whatever special
characters Excel might need to interpret these returns as and a
newline in a cell and not as a new row. Thanks for your help.

Hi

Recently I received a huge list of customers (around 3000) from my accounts department. This excel document contains 2 columns, viz Customer & Address. I am supposed to export this data to another system

The problem is this address column contains actually 4 fields (Door No, Name, City & County). All these fields are concatenated with a square symbol (unable to copy & paste that symbol) and pasted into the Address column. I think this symbol is carriage return character. Now I want to split this particular column into 4 separate columns mentioned above
How can I do this? Can someone let me know please..

Thanks in advance

Harish Mohanbabu

I've pulled some data into excel from a foxpro database. The data comprises notes which were in a general text field.

When they've pulled through, they've also pulled through the carriage return.

I now need to put this information into Sage, and so instead of having a large text field, they have 6 fields of 40 characters each.

What I would like to do is separate the data in the sheet into 6 columns, with the splits being made at each carriage return.

Does anyone know how to do this, without going through manually?

I can't attach a sample file at the moment as this page is showing with errors, so hopefully the above makes sense.

Hello,
I have a file that contains carriage returns in some of the fields. I would
like to get rid of the carriage returns and replace them with a different
character of some sort (a pipe or back-slash). I have tried the replace
tool, but haven't had any luck with that -- even after researching the ASCII
codes to see if there was a code I could search by. The only other tool I
have gets rid of the carriage returns, but doesn't have an option to replace
them with a different character.

Any suggestions?

Thank you very much in advance,

Dan

hello

i have an excel document.
inside a lot of text
how can i get rid of all tabs, carriage returns and new lines characters
that could be in this documents in all fields?

thanks in advance for help

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

I found this thread on the now defunct Google Answers that is spot on to my problem, but their solution didn't work:
http://answers.google.com/answers/threadview?id=319322

As you are probably aware, if you try to simply cut & paste a word table to excel, any carriage returns within a table cell will cause multiple rows in excel, and will cause the rest of the cells in that row that have no carriage returns to appear as merged cells.

In my case, I had a bunch of cells w/ soft carriage returns, but I was able to replace them using "^l" in the Find field in Word.

As described in the above thread, Excel does not recognize such codes as "^p" or "^l" in the Find/Replace fields, and anyway, I cannot simply use the Find/Replace function because I get a "Formula is too long." error.

Here is the code provided by aht-gt in the Google Answers thread:

Public Function ReplaceCharacter(ByVal s As String, ByVal oldchar As String, ByVal newchar As String) As String
Dim sVal As String

'Set the late binding objects
Dim rPart As Object

sVal = s

Set rPart = CreateObject("VBScript.RegExp")
rPart.Global = True
rPart.IgnoreCase = True
rPart.Pattern = oldchar

'replace all occurences of the pattern c with the
sVal = rPart.Replace(sVal, newchar)

Set rPart = Nothing

ReplaceCharacter = sVal

End Function

Sub MyReplace()
Dim s As String

On Error GoTo MyReplaceAbort

Application.DisplayStatusBar = True
Application.StatusBar = "Replacing Characters..."
Application.ScreenUpdating = False

For Each c In Selection.Cells
c.Value = ReplaceCharacter(c.Value, "~", Chr(10))
Next c

MyReplaceAbort:

Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = True

End Sub

I used "qqq" as my dummy string, so I changed the "~" in his code to "qqq".
I ran the macro, and instead of replacing my dummy string w/ a line break, it replaced it with the string "^l". I also tried changing the "Chr(10)" in his code with "Chr(13)" and "Chr(10) & Chr(13)", and the results were the same.
How &^%$#@! asinine is that?
I have thousands of table rows, and it just isn't realistic fixing all of these carriage returns manually.

Can someone please suggest some alternatives here, or possibly explain why his code isn't working?

I'm running Excel 2003 SP 2.

Thanks.

Hi,

I've a need for formating parts of a cell that lie between Square and standard brackets. basically i've written a series of testing instructions for some software and as a shorthand put field names between [] and data for inputing between (). I now think it would help if i could format so field names were [Bold and red] and (data for input). If it was field and data input per cell i could have pulled it off by customising similar code alredy provided in other threads, but the number varies per cell from 0 up to 6 or 7 of each and i end up with just the first set or the whole cell formated.

Example Cell content is (includes Carriage returns)

H1 --- Run 2 variations - i, ii
------ [Consultation Time] = (Blank)
------ [All other Fields] = (As Per Dataset)
------ Click i 'Save & Close' or ii 'Submit' button
.
.
.
H190 -- [17a] = (10.5); [17b] = (10.5); [17c] = (10)
------- [All Other Fields] = (As Per Dataset)
------- Click 'validate/submit' button

and how I would would like it to look

H1 --- Run 2 variations - i, ii
------ [Consultation Time] = (Blank)
------ [All other Fields] = (As Per Dataset)
------ Click i 'Save & Close' or ii 'Submit' button
.
.
.
H190 -- [17a] = (10.5); [17b] = (10.5); [17c] = (10)
------- [All Other Fields] = (As Per Dataset)
------- Click 'validate/submit' button

Hi all,

This problem is driving me bonkers and I was wondering if anyone can help me out with it. I've looked at the forums and I've seen some similar issues but I haven't been able to make it work for me.

So here's my issue.

I have received a file with a column that is full of garbage text. Within this text string is a location that I need to extract. The issue is that the garbage text is inconsistent in the amount of text, type of characters etc.

So imagine in A1 you have "The location of your event is

hard carriage return ABC Church
time is :[12:00]
Now in column B I have a number of locations that I want to find in A1

For example in this column I may have
ABC Church
Varsity Stadium
ABC Library
etc

I need to search through all these garbage strings in column A and when I find a match, return the associated field in column B.

In the above example the code should spit out ABC Church.

I was thinking of doing a VLOOKUP because I would be able to return the any results that match. I haven't been able to get this work. I've attempted using wildcards etc.

Any help would be much appreciated.
Thank you!

Hi,

I have an input form which I wish to proivde information to users on how it should eb filled in. For most of the fields this is a simple 3 or 4 word message, but one field needs a detailed description.

Using the ControlTipText function gives me plenty of characters to do this but does not offer any formatting, so it just reads as one long line of info. Not very pretty.

I have tried inserting vbcrlf commands to split the message but that just gives the two square characters of a carriage return, rather than move to a new line.

I have a horrible feeling that this isn't possible, but maybe someone could suggest a suitable alternative/workaround?

TIA

KJ

Hello folks. Excel newbie here hoping for a point in the right
direction.

I'm a linux girl, I don't do Windows and know nothing about Excel so
please bear with me. I've been asked to solve a problem for my managers
so I'm delving into Excel.

We are exporting data from our DB2 database in roughly the following
format:

"Employee Name", "Date", "Task", "Employee's Task Comments"
"Employee Name", "Date", "Task", "Employee's Task Comments"
"Employee Name", "Date", "Task", "Employee's Task Comments"

You get the idea. The first three fields are not an issue. But that
last field is a free form text field in our application that can (and
usually does) include things like tabs and newline characters. When we
try to import this data into Excel the newlines in the comments field
cause a new row in Excel to be created. This is obviously not what we
want.

What my managers would like to see is the comments preserved the way
they would be seen in our application. IE one cell with tab and newline
characters maintained in that cell. I'm guessing there has to be away
to do this, perhaps when I'm doing the data import from the text file?
(I'm using Data->Import External Data and going throught the wizard.
So I can tell Excel that last field is " delimited text but it still
breaks the field on newlines.)

Any nudges in the right direction would be appreciated. Programming I
know, but Excel is a foreign tool and the help was less than useful.

Chris

I have a table of multiple parts descriptions ranging from 40
characters to 600 characters in length, in order to import this into my
data base the description cannot be longer than 75 characters, how
would i go about seperating the descriptions at the 75 character mark
and then extracting them into a new cell. Here is an example of one of
my descriptions

SAW MOTOR~KF 62.01~CAPACITY : 1.3 KW~MODE OF OPERATION: S1~MOTOR SPEED
: 3430 RPM~VOLTAGE : 460 V~FREQUENCY : 60 HZ~NOM.CURRENT : 2.85
A~PROTECTION IP 54~INSULATION CLASS F~TYPE : B3~TERMINAL BOX : 3B~BRAKE
VOLTAGE : 460 V~BRAKE TORQUE : 4 NM~BLUE-GRAY~COLOR RAL 7031~WITH
THERMAL SWITCH~WT/160 C~CSA/UL - MOTOR TYPE SIGN~K 62 S/2-G4K~PART-NO.
99324899~DOC-NO. 87160003/50

which essentially needs to turn into 4 sets of 75 char cells so that i
can import them into the comment field in the database i.e.

A1 = Saw motor ....................
B1= 330 RPM.............

etc

thanks for all your help

p.s.

i have about 5000 of these descriptions that need to be extracted (i
also have to keep the ~since this specifies the carriage return for the
print outs)

Hi All,

I have a question that might not be suited to this forum but I have an Excel VBA Userform that is a collaboration between different sources... some records are manually entered into the form and others a copy & paste between a pdf or text file into the form...

Question is, is there a property in the textfield control that allows an autofill style of copy and paste so that all textfields on the form are "linked" so that the datafields for that record can be copied and pasted into the form at once and where there is a paragraph character or carriage return, data is flowed onto the next field... the datafields are of variable length and I have tried setting the textcontrol properties to AutoTab is True and EnterKeyBehaviour is True... plus I have all the textfields in tab order.

This would save a fair amount of time, at the moment it is copy each datafield one at a time and paste into the corresponding textfield on the form... there are approx. 50 fields on the form...

Sorry if there is another post out there like this - everytime I search for a few words I get a memory error for the search.

Thanks in advance

Hi all,

I have recently created an entry from which has just gone live. The first issue i have is that the people using it copy and paste into 2 fields on my form from AS400 (JBA601). When pasted, for some reason it copies a carriage return accross which affects the way it is displayed in the underlying sheet of my form. i.e. i have to print a range of my sheet and the carriage return on the first field displays a square and in the second actually creates a return which makes the info not fit in the cell.

What i'm asking is how do i remove the last charater (the carriage return) of the string entered in my txtbox on my form (and any spaces at the end if poss).

Cheers,
Spinkung

Hi all

I have a workbook which imports data each 4-weeks from a csv output from a database
The number of columns with data in can vary
I have written code which filters out the initial results, which put text in columns labelled text 1 to x and look up a user name in the following x columns. Because the database output treats a user-input carriage return as "put the next bit of text in a new field", the last characters ("(user Joe Bloggs)" can be in any of these x columns, although most likely in 1 to 3.
I then need to do an advanced filter based on the number of columns actually with text in them.

I have dealt with getting the criteria range correct, and put the last column letter into a variable. I cannot persuade the advanced filter function to recognise either the variable (eg "AZ") or to use the "Range(cells(a,b),cells(c,d))" syntax

I hope I've explained this - is there a way of doing what I want or have I got to think along different lines?

Thanks in advance

Paul

I have a fixed dimension Textbox on a spreadsheet. I read variable data
(Text) from a database field and insert it into the textbox. The
spreadsheet is eventually printed. I need to check if all of the text
in the textbox is actually visible to the user. i.e too much text will
be clipped. The text can contain carriage return or linefeed
characters.

If not then I would possibly reduce the font size assigned to the text
box until all the Text is visible to the user.

Does anyone know a method to do this?

The text file that i am trying to import looks like this:

Field 1 <tab> Field 2 <tab> Field 3.1(CR)Field 3.2(CR)Field 3.3<tab>Field 4

where <tab> is Tab, and (CR) is carriage return. When importing into Excel, i want all 3 lines in Field 3 (Field 3.1, Field 3.2 and Field 3.3) be in one cell, but multiline. So basically i want excel to ignore carriage return. How do i do that???
I have some control on which characters to use to delimit the text file (e.g. i can change <tab> and (CR) to whatever i want. here is what i have tried so far:

changed (CR) character to carriage return (Ascii 13)
changed (CR) character to Line Feed (Ascii 10)
changed (CR) character to carriage return + Line Feed (Ascii 13 + Ascii 10)
Tried enclosing Field 3.1(CR)Field 3.2(CR)Field 3.3 into single or double quotes.

none of these seem to work: I get this in output:
Field 1 Field 2 Field 3.1
Field 3.2
Field 3.3 Field 4

does anyone know of a solution. it would be greatly appreciated

I receive a database dump from a contract I'm working on that contains 3 different record types. It is a flat file, no delimiters at all, not even carriage returns at the end of records. I will provide a bit of detail about the record types at the end of the post, but the real issue is this: I have found script examples that would allow me to import one of the record types fairly easily, but I haven't seen anything nor do I have the knowledge sufficient to write a script or macro that parses the different record types and imports appropriately, for example:

File ab090110 would contain 25 type 1 records, 4 type 2 records and 3 type 3 records. The type 2 records appear interspersed with the type 1 records and the type 3 records all appear at the end (this week, last week they were all in the middle.)

A record's first field is always 1 character in length and contains a number indicating the record type, so there is something to check for.

Record type 1
First field is 1 character in length and always contains a 1.
Total record is 1050 characters including type identifier field.
Contains 77 fields (including type identifier) of varying lengths.

Record type 2
First field is 1 character in length and always contains a 2.
Total record is 150 characters in length including identifier field.
Contains 13 fields (including type identifier) of varying lengths.

Record type 3
First field is 1 character in length and always contains a 9.
Total record is 150 characters in length including type identifier
Contains 6 fields (including type identifier) of varying lengths.

I have a very basic programming background, and my logic tells me that I would need 3 different functions, one for each record type, chosen at runtime by the field identifier for each record. I would need a method of advancing a counter by the appropriate amount based on the record type since the beginning of record 47 might be 1050 characters after the beginning of record 46, but the beginning of record 48 might only be 150 characters after the beginning of record 47.

I hope I've explained my need clearly. I have no objection to reading help files and the like for specific command syntax, but if someone would be so kind as to point me in the right direction to get started that would be amazing. Thank you very much!


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