how to split text cells into multiple columns in excel

If you copy data from another program and paste it into Microsoft Excel, Excel may condense several columns of data to a single column. You can use the Text To Columns command on the Data menu to put each record in a separate column. The range that you convert may contain many rows, but you can only convert one column of data at a time.

To create an example that demonstrates how to use the Text To Columns command:

  1. Create a new workbook.
  2. In Sheet1, type the following data:
          A1: The,dog,ran
          A2: abc,def
          A3: abcdefg,hijkl
          A4: 2554,4874,41
  3. Select the range A1:A4.
  4. On the Data menu, click Text to Columns.
  5. In Step 1 of the Convert Text To Columns Wizard, make sure that the Delimited option is selected, and then clickNext.
  6. In Step 2 of the Convert Text To Columns Wizard, click to select the Comma check box under Delimiters, and then click Next.NOTE: All of the check boxes under Delimiters should be cleared except for the Comma check box.
  7. In Step 3 of the Convert Text To Columns Wizard, type B1 in the Destination box, and then click Finish.

Your data is parsed into separate columns, beginning with column B, and the data in Sheet1 should resemble the following:

   A1: The,dog,ran     B1: The     C1: dog   D1: ran
   A2: abc,def         B2: abc     C2: def
   A3: abcdefg,hijkl   B3: abcdefg C3: hijkll
   A4: 2554,4874,41    B4: 2554    C4: 4874  D5: 41

Note The example illustrates data with commas as the delimiter. This is only one of several delimiters that you may see in your data. For example, if you have Names in a column of data, consisting of first and last name in a single cell separated by a Space, the Space character can be used as a delimiter to separate the first and last names into adjacent cells instead of the same cell.

How to delete duplicate records from a table in Access 2000

Article ID: 209183 – View products that this article applies to.
This article was previously published under Q209183
For a Microsoft Access 97 version of this article, see 109329.

Novice: Requires knowledge of the user interface on single-user computers.

Expand all | Collapse all

SUMMARY

This article describes how you can remove duplicate records from a table by using primary keys. By using this method, you can check for duplicate values in up to 10 fields in the table.

MORE INFORMATION

To remove duplicate records from a table, follow these steps:

  1. Make a copy of the structure of the table from which you want to remove the duplicate records.To copy a table:
    1. Select the table in the Database window
    2. On the Edit menu, click Copy.
    3. On the Edit menu, click Paste.
    4. Enter a name for the new table.
    5. Select Structure Only
    6. Click OK.
  2. Open the new table in Design view.
  3. Select the field(s) that contain the duplicate values.
  4. To make your selection the primary key for the table, click the Primary Key button on the toolbar.
  5. Save and close the table.
  6. Create an append query based on the original table containing duplicates.
  7. In the query Design View, on the Query menu, click Append Query.
  8. In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.
  9. Include all the fields from the original table by dragging the asterick (*) to the query design grid.
  10. On the Query menu, click Run.
  11. Click Yes in the dialog box advising you that you are about to append records.
  12. Because the Primary Key field(s) in the new table will not accept duplicate values, the following error message will be displayed:
    Microsoft Access can’t append all the records in the append query.Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn’t add <number> record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
    Do you want to run the action query anyway?
    To ignore the error(s) and run the query, click Yes. For an explanation of the causes of the violations, click Help.
  13. Click Yes.
  14. View the contents of the new table. When you’re sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.

REFERENCES

For more information about deleting duplicate records, click Microsoft Access Help on the Help menu, type Automatically delete duplicate records from a table. in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 209183 – Last Review: July 29, 2004 – Revision: 3.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords:
kbhowto kbdatabase kbusage KB209183

HTML Copyright and trademark symbols

Copyright and trademark symbols
Copyright and trademark symbols HTML code

Copyright and registered trademark symbols can not easily be copy pasted into your html webpage so you should use the Copyright or registered trademark html symbol code instead.

Find the Copyright or registered trademark symbol you want below and then copy/paste the html smiley Copyright or registered trademark code highlighted in red into your html document.

 ™

&#8482;

&#8480;

 ©

&#169;

®

&#174;

 ℗

&#8471;

How to: Remove or replace line breaks and carrage returns in cells

There are few quick methods to remove the line breaks and carriage returns in cells.
By default you might not know an easy and quick way to do this but it is fairly simple, either with just Excel, or with the help of ASAP Utilities.

Let’s say we have the following example.
In this case the author has used the shortcut Alt+Enter to put the last name on a new line.Initial dataset
But now we need it together on a single line, with a space between the first- and last name instead of a line break.
Instead of:
Bastien
Mensink

we need
Bastien Mensink, etc. etc.

Method 1, with Excel only

If you want to replace these line-breaks with for example a space, you can do this with Excel’s find/replace command.

In the “Find what:” box you can enter the line-break code, which is character number 10.
To do this, press and hold the Alt key, and then enter the numbers 010 from the numeric part of the keyboard.
On my computer in the box there is then a small dot blinking when the cursor is still in the entry box.
To enter the numbers you need to use the numbers from the Num-lock part of the keyboard (with the numbers above the letters on your keyboard it doesn’t work). If you have data imported from another source, sometimes the line breaks are also represented by character 13 instead of 10.

In the “Replace with:” dialog you can enter a space to have the line-breaks replaced with spaces. You can also leave that blank to have the line-breaks removed.
Excel's Find and Replace dialogue

Method 2, with ASAP Utilities

You can use the following tool from ASAP Utilities to remove or replace the line-breaks:
Text » Advanced character remove or replace…

In the dialogue you can click on the “Line breaks” button to remove or replace the line break characters in the selected cells:Remove the line breaks/carriage returns with ASAP Utilities

Method 3, with ASAP Utilities

You can use the following tool from ASAP Utilities to remove or replace the line-breaks:
Range » Find and/or replace in all sheets…

To search for the line break you can enter the code {lf}, to search for the carriage return you can use {cr}:ASAP Utilities Find and/or replace in all sheets

Filed under: ASAP UtilitiesHow tohow to with ASAP Utilities on January 18th, 2010 by Bastien | 3 Comments »

3 Responses to “How to: Remove or replace line breaks and carrage returns in cells”

  1. You can also use ctrl-j instead of alt-0010 in excel’s edit|replace dialog.

  2. Hello Dave,

    Thank you for your feedback.
    I didn’t know that, thanks for sharing!

    Kind regards,
    Bastien

  3. Kanwaljit, on February 1st, 2010 at 1:49 pm Said:

    Hi Bastien,

    May the Couple Live 1000 years !

    If we put , (comma) when using the File Types option “Text File, Delimited by :” in Utility 16-Import->10 Merge and Import Multiple Files” the Utility starts a give an error message “Error 1004 : Method ~ of Object ~ Failed”. Other file type options seems to be working fine.

    Regards
    CA Kanwaljit Singh Dhunna

Originally posted on ASAP Utilities 

Character Entity References in HTML 4 and XHTML 1.0

We would like to recognize Elizabeth Castro for her great work in compiling this list.

You see more of her work here http://www.elizabethcastro.com/html/extras/entities.html

Here is a set of tables containing the 252 allowed entities in HTML 4 and XHTML 1.0, as described in section 24 of the official HTML 4 specifications, published by the W3C. I have divided them into my own, hopefully logical, categories:

Each table has five columns. The first column contains the entity reference, in the form &entity_name;, that is, an ampersand, the entity name, and then a semi colon. The second column displays how that entity appears in your browser. The proper character will only appear if you have a font that can display it. The third column contains the number reference for the same character in the form &#number;, that is, an ampersand, a hash symbol (which signals that a number reference is coming), the character’s number, and then a semi colon. The fourth column shows how the number reference displays in your browser. Again, the proper character is only displayed if the default font selected in your browser preferences contains such a character.

The fifth column contains a description of the character, and an occasional note. You can either hover over the note link to see the note, or click it to go the notes page, which will open in a separate window.

There are many ways to order character entities. You can order them alphabetically, by number, or by Unicode collection, to mention just a few methods. I find all of these rather arbitrary. So, I have taken the liberty of classifying them into what I consider logical categories, and sometimes subcategories, and then by alphabetical order. I hope you find it useful. And don’t forget, you can always use your browser’s Find command to find a particular word or phrase within this page.

For more information on how to use these entities in your Web pages, see HTML, XHTML, and CSS, Sixth Edition by Elizabeth Castro.

Entities for characters with special meaning in HTML and XHTML

Entity Entity
Displayed
Number Number
Displayed
Description
&amp; & &#38; & ampersand
&gt; > &#62; > greater-than sign
&lt; < &#60; < less-than sign
&quot; &#34; quotation mark = APL quote

Entities for accented characters, accents, and other diacritics from Western European Languages

Entity Entity
Displayed
Number Number
Displayed
Description
&acute; ´ &#180; ´ acute accent = spacing acute
&cedil; ¸ &#184; ¸ cedilla = spacing cedilla
&circ; ˆ &#710; ˆ modifier letter circumflex accent
&macr; ¯ &#175; ¯ macron = spacing macron = overline = APL overbar
&middot; · &#183; · middle dot = Georgian comma = Greek middle dot
&tilde; ˜ &#732; ˜ small tilde
&uml; ¨ &#168; ¨ diaeresis = spacing diaeresis
&Aacute; Á &#193; Á latin capital letter A with acute
&aacute; á &#225; á latin small letter a with acute
&Acirc; Â &#194; Â latin capital letter A with circumflex
&acirc; â &#226; â latin small letter a with circumflex
&AElig; Æ &#198; Æ latin capital letter AE = latin capital ligature AE
&aelig; æ &#230; æ latin small letter ae = latin small ligature ae
&Agrave; À &#192; À latin capital letter A with grave = latin capital letter A grave
&agrave; à &#224; à latin small letter a with grave = latin small letter a grave
&Aring; Å &#197; Å latin capital letter A with ring above = latin capital letter A ring
&aring; å &#229; å latin small letter a with ring above = latin small letter a ring
&Atilde; Ã &#195; Ã latin capital letter A with tilde
&atilde; ã &#227; ã latin small letter a with tilde
&Auml; Ä &#196; Ä latin capital letter A with diaeresis
&auml; ä &#228; ä latin small letter a with diaeresis
&Ccedil; Ç &#199; Ç latin capital letter C with cedilla
&ccedil; ç &#231; ç latin small letter c with cedilla
&Eacute; É &#201; É latin capital letter E with acute
&eacute; é &#233; é latin small letter e with acute
&Ecirc; Ê &#202; Ê latin capital letter E with circumflex
&ecirc; ê &#234; ê latin small letter e with circumflex
&Egrave; È &#200; È latin capital letter E with grave
&egrave; è &#232; è latin small letter e with grave
&ETH; Ð &#208; Ð latin capital letter ETH
&eth; ð &#240; ð latin small letter eth
&Euml; Ë &#203; Ë latin capital letter E with diaeresis
&euml; ë &#235; ë latin small letter e with diaeresis
&Iacute; Í &#205; Í latin capital letter I with acute
&iacute; í &#237; í latin small letter i with acute
&Icirc; Î &#206; Î latin capital letter I with circumflex
&icirc; î &#238; î latin small letter i with circumflex
&Igrave; Ì &#204; Ì latin capital letter I with grave
&igrave; ì &#236; ì latin small letter i with grave
&Iuml; Ï &#207; Ï latin capital letter I with diaeresis
&iuml; ï &#239; ï latin small letter i with diaeresis
&Ntilde; Ñ &#209; Ñ latin capital letter N with tilde
&ntilde; ñ &#241; ñ latin small letter n with tilde
&Oacute; Ó &#211; Ó latin capital letter O with acute
&oacute; ó &#243; ó latin small letter o with acute
&Ocirc; Ô &#212; Ô latin capital letter O with circumflex
&ocirc; ô &#244; ô latin small letter o with circumflex
&OElig; Π&#338; Πlatin capital ligature OE
&oelig; œ &#339; œ latin small ligature oe (note)
&Ograve; Ò &#210; Ò latin capital letter O with grave
&ograve; ò &#242; ò latin small letter o with grave
&Oslash; Ø &#216; Ø latin capital letter O with stroke = latin capital letter O slash
&oslash; ø &#248; ø latin small letter o with stroke, = latin small letter o slash
&Otilde; Õ &#213; Õ latin capital letter O with tilde
&otilde; õ &#245; õ latin small letter o with tilde
&Ouml; Ö &#214; Ö latin capital letter O with diaeresis
&ouml; ö &#246; ö latin small letter o with diaeresis
&Scaron; Š &#352; Š latin capital letter S with caron
&scaron; š &#353; š latin small letter s with caron
&szlig; ß &#223; ß latin small letter sharp s = ess-zed
&THORN; Þ &#222; Þ latin capital letter THORN
&thorn; þ &#254; þ latin small letter thorn
&Uacute; Ú &#218; Ú latin capital letter U with acute
&uacute; ú &#250; ú latin small letter u with acute
&Ucirc; Û &#219; Û latin capital letter U with circumflex
&ucirc; û &#251; û latin small letter u with circumflex
&Ugrave; Ù &#217; Ù latin capital letter U with grave
&ugrave; ù &#249; ù latin small letter u with grave
&Uuml; Ü &#220; Ü latin capital letter U with diaeresis
&uuml; ü &#252; ü latin small letter u with diaeresis
&Yacute; Ý &#221; Ý latin capital letter Y with acute
&yacute; ý &#253; ý latin small letter y with acute
&yuml; ÿ &#255; ÿ latin small letter y with diaeresis
&Yuml; Ÿ &#376; Ÿ latin capital letter Y with diaeresis

Entities for punctuation characters

Entity Entity
Displayed
Number Number
Displayed
Description
&cent; ¢ &#162; ¢ cent sign
&curren; ¤ &#164; ¤ currency sign
&euro; &#8364; euro sign
&pound; £ &#163; £ pound sign
&yen; ¥ &#165; ¥ yen sign = yuan sign
&brvbar; ¦ &#166; ¦ broken bar = broken vertical bar
&bull; &#8226; bullet = black small circle (note)
&copy; © &#169; © copyright sign
&dagger; &#8224; dagger
&Dagger; &#8225; double dagger
&frasl; &#8260; fraction slash
&hellip; &#8230; horizontal ellipsis = three dot leader
&iexcl; ¡ &#161; ¡ inverted exclamation mark
&image; &#8465; blackletter capital I = imaginary part
&iquest; ¿ &#191; ¿ inverted question mark = turned question mark
&lrm; &#8206; left-to-right mark (for formatting only)
&mdash; &#8212; em dash
&ndash; &#8211; en dash
&not; ¬ &#172; ¬ not sign
&oline; &#8254; overline = spacing overscore
&ordf; ª &#170; ª feminine ordinal indicator
&ordm; º &#186; º masculine ordinal indicator
&para; &#182; pilcrow sign = paragraph sign
&permil; &#8240; per mille sign
&prime; &#8242; prime = minutes = feet
&Prime; &#8243; double prime = seconds = inches
&real; &#8476; blackletter capital R = real part symbol
&reg; ® &#174; ® registered sign = registered trade mark sign
&rlm; &#8207; right-to-left mark (for formatting only)
&sect; § &#167; § section sign
&shy; &#173; soft hyphen = discretionary hyphen (displays incorrectly on Mac)
&sup1; ¹ &#185; ¹ superscript one = superscript digit one
&trade; &#8482; trade mark sign
&weierp; &#8472; script capital P = power set = Weierstrass p
&bdquo; &#8222; double low-9 quotation mark
&laquo; « &#171; « left-pointing double angle quotation mark = left pointing guillemet
&ldquo; &#8220; left double quotation mark
&lsaquo; &#8249; single left-pointing angle quotation mark (note)
&lsquo; &#8216; left single quotation mark
&raquo; » &#187; » right-pointing double angle quotation mark = right pointing guillemet
&rdquo; &#8221; right double quotation mark
&rsaquo; &#8250; single right-pointing angle quotation mark (note)
&rsquo; &#8217; right single quotation mark
&sbquo; &#8218; single low-9 quotation mark
&emsp; &#8195; em space
&ensp; &#8194; en space
&nbsp; &#160; no-break space = non-breaking space
&thinsp; &#8201; thin space
&zwj; &#8205; zero width joiner
&zwnj; &#8204; zero width non-joiner

Entities for mathematical and technical characters (including Greek)

Entity Entity
Displayed
Number Number
Displayed
Description
&deg; ° &#176; ° degree sign
&divide; ÷ &#247; ÷ division sign
&frac12; ½ &#189; ½ vulgar fraction one half = fraction one half
&frac14; ¼ &#188; ¼ vulgar fraction one quarter = fraction one quarter
&frac34; ¾ &#190; ¾ vulgar fraction three quarters = fraction three quarters
&ge; &#8805; greater-than or equal to
&le; &#8804; less-than or equal to
&minus; &#8722; minus sign
&sup2; ² &#178; ² superscript two = superscript digit two = squared
&sup3; ³ &#179; ³ superscript three = superscript digit three = cubed
&times; × &#215; × multiplication sign
&alefsym; &#8501; alef symbol = first transfinite cardinal (note)
&and; &#8743; logical and = wedge
&ang; &#8736; angle
&asymp; &#8776; almost equal to = asymptotic to
&cap; &#8745; intersection = cap
&cong; &#8773; approximately equal to
&cup; &#8746; union = cup
&empty; &#8709; empty set = null set = diameter
&equiv; &#8801; identical to
&exist; &#8707; there exists
&fnof; ƒ &#402; ƒ latin small f with hook = function = florin
&forall; &#8704; for all
&infin; &#8734; infinity
&int; &#8747; integral
&isin; &#8712; element of
&lang; &#9001; left-pointing angle bracket = bra (note)
&lceil; &#8968; left ceiling = apl upstile
&lfloor; &#8970; left floor = apl downstile
&lowast; &#8727; asterisk operator
&micro; µ &#181; µ micro sign
&nabla; &#8711; nabla = backward difference
&ne; &#8800; not equal to
&ni; &#8715; contains as member (note)
&notin; &#8713; not an element of
&nsub; &#8836; not a subset of
&oplus; &#8853; circled plus = direct sum
&or; &#8744; logical or = vee
&otimes; &#8855; circled times = vector product
&part; &#8706; partial differential
&perp; &#8869; up tack = orthogonal to = perpendicular
&plusmn; ± &#177; ± plus-minus sign = plus-or-minus sign
&prod; &#8719; n-ary product = product sign (note)
&prop; &#8733; proportional to
&radic; &#8730; square root = radical sign
&rang; &#9002; right-pointing angle bracket = ket (note)
&rceil; &#8969; right ceiling
&rfloor; &#8971; right floor
&sdot; &#8901; dot operator (note)
&sim; &#8764; tilde operator = varies with = similar to (note)
&sub; &#8834; subset of
&sube; &#8838; subset of or equal to
&sum; &#8721; n-ary sumation (note)
&sup; &#8835; superset of (note)
&supe; &#8839; superset of or equal to
&there4; &#8756; therefore
&Alpha; Α &#913; Α greek capital letter alpha
&alpha; α &#945; α greek small letter alpha
&Beta; Β &#914; Β greek capital letter beta
&beta; β &#946; β greek small letter beta
&Chi; Χ &#935; Χ greek capital letter chi
&chi; χ &#967; χ greek small letter chi
&Delta; Δ &#916; Δ greek capital letter delta
&delta; δ &#948; δ greek small letter delta
&Epsilon; Ε &#917; Ε greek capital letter epsilon
&epsilon; ε &#949; ε greek small letter epsilon
&Eta; Η &#919; Η greek capital letter eta
&eta; η &#951; η greek small letter eta
&Gamma; Γ &#915; Γ greek capital letter gamma
&gamma; γ &#947; γ greek small letter gamma
&Iota; Ι &#921; Ι greek capital letter iota
&iota; ι &#953; ι greek small letter iota
&Kappa; Κ &#922; Κ greek capital letter kappa
&kappa; κ &#954; κ greek small letter kappa
&Lambda; Λ &#923; Λ greek capital letter lambda
&lambda; λ &#955; λ greek small letter lambda
&Mu; Μ &#924; Μ greek capital letter mu
&mu; μ &#956; μ greek small letter mu
&Nu; Ν &#925; Ν greek capital letter nu
&nu; ν &#957; ν greek small letter nu
&Omega; Ω &#937; Ω greek capital letter omega
&omega; ω &#969; ω greek small letter omega
&Omicron; Ο &#927; Ο greek capital letter omicron
&omicron; ο &#959; ο greek small letter omicron
&Phi; Φ &#934; Φ greek capital letter phi
&phi; φ &#966; φ greek small letter phi
&Pi; Π &#928; Π greek capital letter pi
&pi; π &#960; π greek small letter pi
&piv; ϖ &#982; ϖ greek pi symbol
&Psi; Ψ &#936; Ψ greek capital letter psi
&psi; ψ &#968; ψ greek small letter psi
&Rho; Ρ &#929; Ρ greek capital letter rho
&rho; ρ &#961; ρ greek small letter rho
&Sigma; Σ &#931; Σ greek capital letter sigma
&sigma; σ &#963; σ greek small letter sigma
&sigmaf; ς &#962; ς greek small letter final sigma (note)
&Tau; Τ &#932; Τ greek capital letter tau
&tau; τ &#964; τ greek small letter tau
&Theta; Θ &#920; Θ greek capital letter theta
&theta; θ &#952; θ greek small letter theta
&thetasym; ϑ &#977; ϑ greek small letter theta symbol
&upsih; ϒ &#978; ϒ greek upsilon with hook symbol
&Upsilon; Υ &#933; Υ greek capital letter upsilon
&upsilon; υ &#965; υ greek small letter upsilon
&Xi; Ξ &#926; Ξ greek capital letter xi
&xi; ξ &#958; ξ greek small letter xi
&Zeta; Ζ &#918; Ζ greek capital letter zeta
&zeta; ζ &#950; ζ greek small letter zeta

Entities for shapes and arrows

Entity Entity
Displayed
Number Number
Displayed
Description
&crarr; &#8629; downwards arrow with corner leftwards = carriage return
&darr; &#8595; downwards arrow
&dArr; &#8659; downwards double arrow
&harr; &#8596; left right arrow
&hArr; &#8660; left right double arrow
&larr; &#8592; leftwards arrow
&lArr; &#8656; leftwards double arrow (note)
&rarr; &#8594; rightwards arrow
&rArr; &#8658; rightwards double arrow (note)
&uarr; &#8593; upwards arrow
&uArr; &#8657; upwards double arrow
&clubs; &#9827; black club suit = shamrock
&diams; &#9830; black diamond suit
&hearts; &#9829; black heart suit = valentine
&spades; &#9824; black spade suit (note)
&loz; &#9674; lozenge

The information in this document is Copyright © 1994-2002 W3C ® (Massachusetts Institute of TechnologyInstitut National de Recherche en Informatique et en Automatique,Keio University), All Rights Reserved. http://www.w3.org/Consortium/Legal/

The layout of this document is copyright © 2002 Elizabeth Castro. Please don’t copy this document. Instead link to it. Thanks!