Split Text To Columns In For Addresses

Split Text To Columns In For Addresses Using
Split Text To Columns In For Addresses
Split Text To Columns
Split Text To Columns In For Addresses Online
Split Text To Columns In Numbers

Select the column with the addresses in it. In our example, column B has the addresses so we would select the entire column by clicking on the letter B at the top of the column. Select the text to columns function from the data menu. When the text to columns wizard opens, select Delimited and click Next. With the new column selected, click the Text to Columns button on the Data tab. This will open up the three-part Text to Columns Wizard. For the first step, ensure that the Delimited button is selected and hit Next. In the second step, select Space for the type of delimiter that we want to split. Below are the steps that will split multiple lines in a single cell into separate cells: Select the entire dataset that you want to split. Go to the Data tab. In the Data Tools group, click on the Text to Columns option. In the Text to Columns dialog box, in Step 1 of 3, select Delimited and click ‘Next’.

With this tool, you can split any text into pieces. You can do it in three ways. The first is to specify a character (or several characters) that will be used for separating the text into chunks. For example, if the input text is 'fan#tas#tic' and the split character is set to '#', then the output is 'fan tas tic'. This article aims to show how to split a text to multiple columns. Steps to perform: In the Tableau, connect to the databases. Let’s connect to a data as I have shown in the image given below. That’s a Product data named as Namedata. Increase the size of the Product Combined column, you can see that you have two different values.

Text to Columns is a Calc dialog introduced in version 2.4.1 which allows you to perform many types of data transformations:

Conversion of text to numbers/dates/times (needed if you're receiving #VALUE! errors)
Conversion of numbers/dates/times to text
Elimination of the initial apostrophe which prevents values from being recognized as numbers/dates/times
Conversion of dates which don't match the locale's format (DD/MM/YYYY to MM/DD/YYYY or vice versa)
Splitting data into multiple delimited fields (separated by commas, tabs, spaces, etc.)
Splitting data to multiple fixed width fields
Conversion of formula results into values
Conversion of text into formulas
Conversion of values into formulas
Fixing dates where the month and day numbers are reversed

Calc formats each cell in a column as specified by the cell's style (Format → Styles and Formatting) and/or direct formatting (Format → Cells). This formatted value is parsed into fields, as specified in the dialog, then the fields replace the contents of that cell and the cells to its right. Much of this feature's power is due to the opportunity to specify the Format Code prior to parsing. These codes are described in Help → Index → number formats;codes. Text to Columns also provides an easy method to separate data into fields using delimiters, something that can be difficult to accomplish with formulas or with Regular Expressions.
Make a contiguous selection of cells from exactly one column, then select menu Data → Text to Columns. In the dialog, choose whether you're separating fields by position (the Fixed Width option) or by delimiters (the Separated By option). For the Fixed Width option, click on the dialog's ruler to set or clear field position breaks. You can avoid field separation by not indicating any positions. For the Separated By option, specify one or more of the Field Separators. Checkboxes are provided for Tab, Comma, Semicolon, and Space. You can supply up to ten additional Field Separators in the Other box. Characters specified in the Other box are case sensitive. You can avoid field separation by not indicating any separators. The Text Delimiter option allows you to specify that Field Separators between the Text Delimiter characters are to be included in the field; they don't start a new field. The dropdown allows you to select a single quote or a double quote, but you can type any Text Delimiter character you want (at most one) in that dialog box. Neither the Field Separators nor the Text Delimiters become part of their field. There is no option to indicate usage of an escape character during Text to Columns; you must use Field Separators if any fields contain your separator characters. The Merge Delimiters option means that sequences of several Field Separators are treated as one. This is likely to be useful when your Field Separator is Space.
After indicating your fields, you can select how each of them is to be processed. In the first row of the large box at the bottom of the dialog, select a field by clicking it. Select/deselect multiple fields using ⌘Click (on a Mac) or Ctrl+Click (on other platforms). Select all fields by clicking the area at the left of the first row, above the row numbers. Then set each field's type using the Column Type box.

Standard means the formatted value is processed with Calc's data entry rules for the locale. This choice allows you to convert text to numbers and times. More details are available in 5. Understanding data entry of Ten concepts that every Calc user should know.
Text means the formatted value will be entered in the cell exactly as is, without any interpretation, and will be stored as text. You should then set the cells' style/format to specify Numbers → Category → Text.
Date means that Calc will try to interpret the formatted value as a date, with components in the order specified, DMY, MDY, or YMD. This choice can be especially useful when you have dates that have been entered in an order different than is customary for the locale. For value 01/02/03, DMY reads it as February 1st 2003, MDY reads it as January 2nd 2003, and YMD reads it as February 3rd 2001. You can also process dates with 4-digit years.
US English means the formatted value is processed looking for numbers as expressed in the US English locale.
Hide means the field will be ignored and not inserted in the spreadsheet's cells. This choice is used when you want to skip over portions of the data in a cell. If you have many fields and want to hide most of them, it's easier to select them all and set Hide, then change a few to some other type.

After setting the field types, click OK. When you have one field, it replaces the cell that was processed. When you have more than one field, the first one replaces the cell that was processed and the other non-Hide fields are stored to its right, overlaying any data that was there, however fields which are empty do not overlay data. To prevent overlaying data, ensure that you have an appropriate number of empty columns to the right of the one being processed. Hidden/filtered rows do participate in the operation. If you get unexpected results after running Text to Columns, you can use Edit → Undo to restore the changes, then try again with different dialog settings.
A similar Text Import dialog is used in three situations in addition to Text to Columns in the Data menu.

File → Open → Type → Text CSV
Insert → Sheet from file → Type → Text CSV
Edit → Paste Special → Unformatted text (when the clipboard contains at least two lines of text)

Note: This tutorial was written for OOo 3.2, and later versions of Apache Open Office, OOo, LibreOffice, NeoOffice, etc. may offer additional facilities in the Text to Columns dialog. Topics in the forums suggest that most people will want to choose the 'Detect Special Numbers' option if it's available (see Q41/A41). You may also want to turn off the Quoted Fields As Text option, if you have quoted fields that contain numeric values. Very old versions (before OOo 2.4?) of OOo will not include the Text to Columns feature, but you can install a later version if you need it.
Q00. I'm trying to convert text to numbers but nothing changes when I run Text to Columns. What's wrong?
A00. If your column has text values, and you want to use field type Standard, change your cells' style/format to a numeric category (Numbers → Category → Number) before running Text to Columns. You can check for text values using View → Value Highlighting; cells containing text will be black, cells containing formulas will be green, and everything else will be blue. Turn off Value Highlighting after checking values because it can obscure cell colors and other formatting. Note that Value Highighting checks the values, not what has been specified for the cell's formatting. If you have text in a cell and just changed the cell's format to Category → Number, the cell still contains text and will be shown as black until you run Text to Columns.
Q01. How can I put the parsed values in a different column? I want to preserve the original values that I have.
A01. First, copy the values to another column using formulas or using Edit → Copy then Edit → Paste Special → Values. Run Text to Columns on the other column.
Q02. How do I use Text to Columns to remove the initial apostrophe in cells?
A02. The apostrophe indicates that the cell is formatted as a number/date/time and the cell's value looks like a number/date/time, however the value is text instead of a number. Value Highlighting will show it as black. Read 1. Types of data in Ten concepts that every Calc user should know if you don't understand the difference between numbers and text. The apostrophe is just a visual indicator for this situation; the cell's value does not contain an apostrophe. You can eliminate the indicator one of two ways: (A) change your cells' style/format to Text or (B) convert your cells' values from text to numbers/dates/times. Changing the display format (A) is accomplished with the Format → Styles and Formatting or the Format → Cells dialog however the cells' values remain as text so you should not perform numeric calculations with them. Changing the values (B) can be accomplished with the Text to Columns dialog using Standard/Date/US English for the field type. Note that (B) cannot be accomplished by changing the cells' display formats because changing display formats never changes cell values, so they remain as text. To change text to numbers, use Text to Columns.
Q03. Some of my fields use separators but other fields are fixed length. How do I use both the Fixed Width and Separated By options?
A03. You'll need to run Text to Columns more than once if you need to use both options. There's an example in topic Separate address-chunk: 3 columns instead of 1.
Q04. The values that I see in the Text to Columns preview dialog don't match the values that I see displayed in the cells. Why?
A04. The values displayed in the cells depend on the applied style/formatting and any applicable styles from Format → Conditional Formatting. The formatted values used by Text to Columns processing do not consider any conditional formatting.
Q05. Instead of splitting data into multiple columns, I'd like to combine multiple columns into one, like FirstName and LastName into just Name. Can I use Text to Columns for that?
A05. No, but you can concatenate cells in your first row using a formula =CONCATENATE(cell;' ';cell) and then fill the formula down the column.
Q06. Text to Columns works fine for converting one column of text values into numbers, but I have 25 to process. How can I do all of them at once?
A06. With Text to Columns, you can't do them all at once. But, in many situations you can use Edit → Find & Replace as explained in Format Cell option Is not able to change Format of Cell.
Q07. I have United States postal codes (ZIP codes) that are missing the leading zero(s). How do I correct that?
A07. These codes should be stored as text, not as numbers, since you're not going to be performing any arithmetic on them. If you're pasting them in from another program be sure to use Edit → Paste Special → Unformatted Text so that the Text Import dialog will appear and you can indicate that the values are text so the leading zeros will be preserved. If you just used Edit → Paste and they have turned into numbers, you can apply a style/format with Numbers → Format Code → 00000. Run Text to Columns with a Text field type to convert your numbers to text. Finally, apply a style/format which specifies Numbers → Category → Text.
Q08. I have dates stored numerically like 19411207 for December 7th 1941. The date formats produce mysterious results: MMMM D, YYYY gives January 28, -10490. How do I make this work?
A08. Read 3. Dates in cells in Ten concepts that every Calc user should know. To convert these numbers to dates, remove your date format and reapply the Default style (Category → Number → General) so you see 19411207 in the cell. Run Text to Columns and use a field type of Date (YMD). Then apply your desired date style/format to the cells.
Q09. I have a field which contains both a date and a time. What field type should I use?
A09. Use the Date type that matches the order (DMY, MDY, or YMD) for your dates and this will process both the date and the time, as long as the time follows the date. If times precede the dates, you'll have to initially create two separate fields and add them with a formula as explained in 4. Times in cells of Ten concepts that every Calc user should know.
Q10. I have cells with numbers that use commas as the decimal separator. My spreadsheet isn't recognizing them as numbers. How do I convert them?
A10. Presumably your locale uses period as the decimal separator. You could change the style/format of these cells to a locale which uses comma as the decimal separator, for example Format → Cells → Numbers → Language → French (France), then run Text to Columns with a Standard field type to convert the values to numbers. You can then apply the Default style if you want these numbers displayed with periods as the decimal separator. However, it's probably easier in many situations to use Edit → Find & Replace to change the commas into periods. If you're going to use Find & Replace and will paste these values in from a non-spreadsheet program, be sure to use Edit → Paste Special → Unformatted Text so you get the Text Import dialog and can mark the field as Text. If you use Edit → Paste with the English (USA) locale, some values like 1,5 (one and a half) will be treated a text but others like 1,625 (one and five eights) will become numbers (one thousand six hundred twenty five).
Q11. I have times stored numerically like 1234 for 12:34 PM. When I format the cells as HH:MM, all my cells show 00:00. How do I make this work?
A11. Read 4. Times in cells in Ten concepts that every Calc user should know. To convert these numbers to times, temporarily apply a style/format to the cells with Format Code 00':'00. Run Text to Columns with field type Standard. Then apply a style/format to the cells with Format Code HH:MM or HH:MM AM/PM.
Q12. What's the difference between changing the display format in the previous example with direct formatting (Format → Cells) instead of a style (Format → Styles and Formatting)?
A12. Normally it's better to use styles in Open Office. But for a temporary change, there are some advantages to using direct formatting. First, you do not need to assign a new style name and then alter the style's number format; you can do it in one step with Format → Cells. Second, after you've run Text to Columns and no longer want to use the temporary format, you can eliminate it using Format → Default Formatting (Control M key), which removes the direct formatting and reverts back to the applied style's formatting. But if you have to run Text to Columns on multiple columns, it may be easier to use a new style even though you have to create it initially and (optionally) delete it when you're done.
Q13. I have times stored numerically like 123456 for 12:34:56 PM. How can I convert these values to times?
A13. Temporarily apply a style/format to the cells with Format Code 00:00:00. Run Text to Columns with field type Standard. Then apply a style/format to the cells with Format Code HH:MM:SS or HH:MM:SS AM/PM.
Q14. I have times stored numerically like 1234 for 12 minutes and 34 seconds. How can I convert these values to times?
A14. Temporarily apply a style/format to the cells with Format Code '00:'00:00. Note that this adds 00: before each value. Run Text to Columns with field type Standard. Then apply a style/format to the cells with Format Code MM:SS.
Q15. I want to discard everything after column 80 in my cells. How do I do that?
A15. Use the Fixed Width option and put a field break at column 80. Set the type of the first field to Text and the type of the second one to Hide.
Q16. I have 20-digit barcodes. When I enter them in my spreadsheet, Calc displays them in exponential notation (31415926535897932384 becomes 3.14159265358979E+019). How can I fix that?
A16. You can't fix this with Text to Columns, because when you entered the value in a cell that wasn't marked as Text (Numbers → Category → Text), Calc interpreted it as a number, and Calc retains only the first 15 significant digits for numbers. The additional 5 digits have been discarded. They are gone and you cannot get them back without re-entering the value(s). If you try to apply Format Code 00000000000000000000 and run Text to Columns, you'll get 31415926535897900000. You must first apply a style/format which specifies Numbers → Category → Text. Then enter your data. If you've imported external data using the Text Import dialog and incorrectly specified the Field Type as Standard instead of Text, values can be stored in exponential notation. You must re-import with a Field Type of Text.
Q17. I have data with up to four fields. Some rows have one field, some have two, some have three, and the others have four. I see how to get just the first field (hide the other three). How can I get just the last field of each row?
A17. This is accomplished with a three step process. Run Text to Columns and hide the first three fields, keeping only the fourth. Run Text to Columns again and hide the first two fields, keeping only the third. Run Text to Columns once more and hide the first field, keeping the second. You'll probably have to try this method before you understand why it works. More complicated situations can be handled with regular expressions in the Find & Replace dialog. There's an example in topic Importing addresses.
Q18. I have some data that I've pasted from the web and the numbers are being interpreted as text. The difficulty is that the cells contain 'non-breaking spaces'. Can I use Text to Columns to get rid of them so that Calc will see my values as numbers?
A18. Possibly, depending on the specific situation. It's easy to get rid of them if they occur after the numbers because you can copy one of the non-breaking spaces to the clipboard and then paste it into the Text to Columns dialog as your Field Separator (Other). And it's easy to get rid of them if there are always a fixed number preceding the numbers because you can use the Fixed Width option, put the field break after these characters and set the first field type to Hide. For most situations, you may find it easier to use the Edit → Find & Replace dialog to remove them. If you suspect that this could be your problem, run that dialog and use More options, select Regular Expressions, Search for x00A0, leave the Replace With field empty, Replace All. I recommend unchecking Regular expressions, before clicking Close.
Q19. I want to convert any negative numbers to positive. How do I do that?
A19. Temporarily apply a style/format to the cells with Format Code General;General;General. Run Text to Columns with field type Standard. Then reapply the default (or other) style. (To convert positive numbers to negative you'd use Format Code -General;-General;General.) If you just want to change how the numbers are displayed, but preserve their signs in the cells, the Format Code change is all you need, so don't run Text to Columns to change the values.
Q20. I want to round numbers to three decimal places. How do I do that?
A20. To change how the values are displayed, apply a style/format to the cells that specifies Format Code 0.000. This changes what is displayed for the cell, but not the cell values, as explained in 2. Controlling how data is displayed in Ten concepts that every Calc user should know. If you also want to change the values in the cells so they are rounded, run Text to Columns with field type Standard. Then reapply the default (or other) style. You could also consider the OpenOffice.org Calc → Calculate → Precision as Shown option (OpenOffice.org → Preferences on a Mac, Tools → Options on other platforms), though that will affect every cell in the spreadsheet.
Q21. I want to truncate my numbers to three decimal places. How do I do that?
A21. Temporarily apply a style/format to the cells with Format Code

0.000 0. Note that there is a space before the final zero. Run Text to Columns with a Field Separator of Space. Set the first field type Standard and the second type to Hide. Then reapply the default (or other) style.
Q22. I want to round my numbers to five significant digits. How do I do that?
A22. Temporarily apply a style/format to the cells with Format Code 0.0000E+000. Run Text to Columns with field type Standard. Then reapply the default (or other) style.
Q23. I have a column of dates but they have the wrong year. It should be 2011. How can I fix that?
A23. Temporarily apply a style/format to the cells with Format Code '2011-'MM-DD. Run Text to Columns with field type Date (YMD). Then reapply the desired date style. You can apply similar techniques to fix the month or day.
Q24. I have names written as Last, First and I want to change them to First Last. How can I do that?
A24. Temporarily apply a style/format to the cells with Format Code @ @. Run Text to Columns with a Field Separator of Comma. Hide the first field; set the second to Text; hide the third field. Reapply a style/format which specifies Numbers → Category → Text. This is another of those cases where you'll probably have to try this method before you understand why it works. If this process leaves an extra space at the beginning of each cell, run Text to Columns a second time and remove it with the Fixed Width option and field type Hide.
Q25. How do I use Text to Columns to convert formula results into values?
A25. To obtain the formatted values for the dialog, Calc has to evaluate each formula, so each formula's result is used during Text to Column processing and replaces any formulas in the column. Note that, depending on the format of the cells being processed, the formatted result may differ from the formula value. For example, it may be rounded as specified by the format. If you intend to keep exactly the same values in the cells, you must ensure that the specified format represents the true value of each cell. Because of this difficulty, people normally convert formula results to values using Edit → Paste Special → Text/Numbers/Date/Time instead of using Text to Columns.
Q26. Is it really possible to convert values into formulas with Text to Columns?
A26. Yes, but in limited situations. For example, if you have a column of numbers, you can temporarily apply a style/format to the cells with Format Code '=SQRT('General')', then run Text to Columns with field type Standard and remove the temporary format. This replaces a cell containing 5 with the formula =SQRT(5). As explained in the preceding question, a second run of Text to Columns would convert this formula to the value 2.236…. But there are limits to what one can accomplish with Format Codes. If you try to format 19411207 into a date formula =DATE(1941;12;07) you'll find that the semicolons in the formula cause trouble because they have special meaning on the Format Code field. Because of this difficulty, people normally construct formulas from values using Edit → Find & Replace.
Q27. I have a column of dates and I want to be able to copy/paste between spreadsheets that use different base dates as discussed in Help → Index → dates;default (Calc). How can I avoid problems with dates in this situation?
A27. Temporarily apply a style/format to the cells with Format Code '=DATEVALUE'('YYYY-MM-DD') then run Text to Columns with field type Standard. Remove the temporary style/format and apply your preferred date style/format. Your dates are now formulas that can be copied to a Calc spreadsheet which uses any base date. The formula for a cell that contained the date December 7th 1941 would be =DATEVALUE('1941-12-07').
Q28. I a column with TRUE/FALSE values. How can I convert them to 'Yes' or 'No'?
A28. Temporarily apply a style/format to the cells with Format Code 'Yes';;'No' then run Text to Columns with field type Text. Finally, apply a style/format which specifies Numbers → Category → Text. If you just want to change how the TRUE/FALSE values are displayed, the first step is all that's needed; you don't need to run Text to Columns.
Q29. I a column with 'Yes'/'No' values. How can I convert them to TRUE or FALSE?
A29. This is more involved than the previous question. Temporarily apply a style/format to the cells with Format Code ='@'='Yes' then run Text to Columns with field type Standard. Apply a style/format which specifies Numbers → Category → Boolean Value. Run Text to Columns twice more with field type Standard. It's probably simpler to perform TRUE ←→ 'Yes' and FALSE ←→ 'No' conversions with Find & Replace.
Q30. I want to split cells vertically instead of horizontally. How do I do that? I want three cells in a row
A30. There is no 'Text to Rows' feature to parse the values in a row and split them vertically. But you can use Edit → Copy with Edit → Paste Special → Transpose to copy the row and put it in an unused column, run Text to Columns on that column, and then use Paste Special again to transpose the result and store it on top of the original cells.
Q31. But is it possible to split the cells in a column vertically (adding new rows) when Text to Columns breaks the data into fields?
A31. Yes, but only when a cell's value uses the NewLine character (Unicode x000A) or Carriage Return character (Unicode x000D) within the cell. You can create a new line in a cell using ⌘Enter (on a Mac) or Ctrl+Enter (on other platforms). During Text to Columns, each NL or CR writes the following data to a new row. For example three cells, each containing Data+NL+Data will create six rows. If you have different delimiters, say comma instead of NL, you would first change comma to NL with Find & Replace. There is no easy way to specify a NL or CR in the Replace With box; you must use the facilities of your operating system to put the character in the box. On a Mac, you would use Show Character Viewer in the Menu Bar to insert NL in the box. Presumably there are similar facilities for other platforms.
Q32. Can Text to Columns also combine/merge cells vertically?
A32 Yes. You can use the Text Delimiter character to continue reading past the end of the cell into the one below it! For example, to combine three cells into one, put your Text Delimiter at the beginning of the first cell and at the end of the third cell. The parsing result will be one cell containing three lines (CellOneData+NL+CellTwoData+NL+CellThreeData). Either add those Text Delimiters manually or use Find & Replace to insert them. Later you could use Find & Replace to convert NL to something else.
Q33. I have a column with formulas and some of them produce errors like #DIV/0!, #NUM!, and #VALUE!. I'd like to replace those with my own 'Missing' message. How can I do that?
A33. Neither the cell's format nor it's conditional format can change the error messages that are produced by the formulas. Since Format Codes do not apply to error values, the best you can do using formatting is make the messages invisible by using conditional formatting to set the cells' text color to match the background color. To alter the message, you'll need to replace the formulas producing errors. Text to Columns can convert just those, leaving all the non-error formulas alone. Temporarily apply a style/format to the cells with Format Code ;;; which will hide all non-error results. Run Text to Columns with field type Text. The formulas producing errors are replaced by the error text ('#DIV/0!', '#NUM!', '#VALUE!', …) but other formulas are not changed. Remove the temporary formatting. Now you can use Find & Replace to convert the error text to whatever you want. Because this process replaces the original formulas, you may want to perform it on a copy of the data. Instead of using Text to Columns, it's probably better to handle this by performing the calculation in an offscreen column and testing for the error as explained in Q2/A2 of VLOOKUP questions and answers.
Q34. I have a column of email addresses, like person@openoffice.org, without the mailto: prefix. How can I convert them to clickable links?
A34. Use these steps. All of the eight quotes shown in the first step should be typed into the Format Code box. Be sure to select the column, for example click the A above cell A1, in each of the four steps. If your column has a label in the first row's cell, use Format → Default Formatting on that cell before proceeding to step 2.
Select the column with the email addresses then: Format → Cells → Numbers → Format Code → '=HYPERLINK(''mailto:'@')'
Select the column with the email addresses then: Data → Text to Columns → OK
Select the column with the email addresses then: Format → Default Formatting
Select the column with the email addresses then: Data → Text to Columns → OK
If you do have the mailto: prefix in the cell, use format code '=HYPERLINK('@')' instead
Q35. I have a column of web addresses, like www.openoffice.org, without the http:// prefix. How can I convert them to clickable links?
A35. Use these steps. All of the eight quotes shown in the first step should be typed into the Format Code box. Be sure to select the column, for example click the A above cell A1, in each of the four steps. If your column has a label in the first row's cell, use Format → Default Formatting on that cell before proceeding to step 2.
Select the column with the web addresses then: Format → Cells → Numbers → Format Code → '=HYPERLINK(''http://'@')'
Select the column with the web addresses then: Data → Text to Columns → OK
Select the column with the web addresses then: Format → Default Formatting
Select the column with the web addresses then: Data → Text to Columns → OK
If you do have the http:// prefix in the cell, use format code '=HYPERLINK('@')' instead
Q36. I have a column of numbers representing dates in an unusual format; 1140501 means 2014-05-01. How can I convert these to Calc dates? I don't understand why there is an extra '1' before '14'.
A36. Dates in that format were used by some people during Y2K transition when a 2-digit year format like 991231 was followed by 1000101, creating a 3-digit year. The digits to the left of the month and day represent the number of years after 1900.
Select the column then: Format → Cells → Numbers → Format Code → '=1900+'000&'-00-00'
Select the column then: Data → Text to Columns → OK. Your numbers have been converted to formulas.
Select the column then: Format → Default Formatting

Split Text To Columns In For Addresses Using

Select the column then: Data → Text to Columns → OK. Your formulas have been converted to Calc dates.
Apply a style/format to the cells so the dates are displayed the way you prefer, prehaps DD MMM YYYY or MM/DD/YYYY.
Q37. I'm splitting a column using comma as the field delimiter. When the cell begins with a comma, indicating the first field is missing, I get strange results. For example, when the cell contains ,y,z Calc puts y and z in the next cells, as expected, but it leaves ,y,z in the first cell instead of clearing it. What's wrong?
A37. Calc handles empty fields in an unexpected way. When there is no data to go into a cell, instead of being cleared, it is left unchanged! While this can be a useful feature, it is not what most people want. The solution is to ensure that the first field is not empty. It's probably easiest to do this by selecting the column, then using Edit → Find & Replace → More Options → Current selection only → Regular Expressions, Search for ^, (caret comma), Replace with ◊, (space comma), Replace All, where ◊ represents a space character. The use of ^ is explained in the link in the previous sentence. (I recommend unchecking Current selection only and Regular expressions

, before clicking Close.) This inserts a space before any comma that was the first character in the cell so the field contains a space instead of being empty, and that space will be placed in the cell. If the cell must truely be empty search for ^, (caret comma), replace with ]]], (bracket bracket bracket comma), run Text to Columns, and then replace ]]] with nothing.
Q38. Will the 'empty field' feature described in A37 cause similar results in cells containing the NewLine character (Q31/A31)?
A38. Yes. If the first character in a cell is a NewLine, or if the cell contains consecutive NewLines, that will create an empty field and the destination cell will not be changed. The resulting column will contain an unusable mixture of changed and unchanged cells. Before running Text to Columns, Use Edit → Find & Replace to ensure that this situation will not occur. See Splitting big cells isn't splitting....
Q39. My formulas aren't being evaluated. I've checked that option OpenOffice Calc → View → Display → Formulas is unchecked. How do I use Text to Columns to correct this problem?
A39. This problem can occur in two situations. It happens when formulas have been entered into text cells (formatted as Numbers → Category → Text). Anything typed in a text cell is treated as text, even if it looks like a formula. First, read Q00/A00 and change the style/formula in the cells to something other than Text. If your formulas will produce currency values use Category → Currency. If they will produce dates, use Category → Date, etc. If you're unsure use Category → All → Format → General. Select the column then: Data → Text to Columns → OK. You can make further adjustments to the style/format after running Text to Columns. The other situation is when you use a formula to create another formula. That is, the result of a formula begins with an equal sign and is followed by valid formula syntax for example ='='&A2&'+'&B2. Calc displays, but does not evaulate, the second formula. To convert the formula result into a new formula select the column then: Data → Text to Columns → OK.
Q40. I have a column of dates where the month and day values are reversed. How can I fix these dates?
A40. This problem can happen when dates are imported from an external source, either with Copy/Paste, or with File → Open → FileType → Text CSV. Select the column. Use Data → Text to Columns. Set the Field Type (read about that in the fourth paragraph of the tutorial) to either Date (DMY) or Date (MDY), depending on how the dates have been entered. For example, if 1/4/2000 is supposed to mean April 1, choose Date (DMY). If 1/4/2000 is supposed to mean January 4, choose Date (MDY). Do not use the default of Standard. Click OK after setting the correct field type. This procedure works even if some of the dates have been accepted with incorrect values and some have been accepted as text since they aren't dates in your locale. For example, English (USA) expects the month first, so if you've entered DD/MM/YYYY dates with April Fool's Day as 1/4/2000 and Halloween as 31/10/2000, the former is taken to be January 4 and the latter is text since there is no 31st month in that locale. Date (DMY) will convert them to April 1 and October 31.
Q41. Should I use the Detect Special Numbers option?
A41. That depends on your data and your requirements. Without this option, only values consisting of digits, one decimal separator, and one thousands separator are interpreted as numeric. All other values are imported as text. Therefore values with currency symbols, scientific notation (31.4159E-1), percent signs (%), dates, times, boolean words TRUE and FALSE, etc. all become text. You will experience difficulty (#VALUE! errors) if you import these values as text and then attempt to perform calculations with them. Most people should use the Detect Special Numbers option.
Q42. I have date/time stored numerically like 20000102030405 for 2000 Jan 02 03:04:05 AM. How can I convert these values to Calc date/times?
A42. Temporarily apply a style/format to the cells with Format Code 0000-00-00 00:00:00. Run Text to Columns with field type Standard. Then apply a style/format to the cells with a date/time Format Code of your choosing, say DDD DD/MMM/YYYY HH:MM:SS.
Q43. I want to discard a # and everything after it in my cells. How do I do that?
A43. Use Separated By → Other → #. Set the type of the first field to Text and the type of the second one to Hide.
Q44. I have multiple-character separators, for example The Lord of the Rings - J. R. R. Tolkien where space dash space is my separator. Can I enter multiple characters in the Other field for Separated By?
A44.

Split Text To Columns In For Addresses

You can, but doing so will not work as desired. When you enter multiple characters in the Other field, this means that any

Split Text To Columns

of them is a separator. Text to Columns will not treat them as a group. Entering space dash space in the Other field means that space or dash is separator. Before using Text to Columns, use Edit → Find&Replace to change the multiple characters into a single character which will not otherwise appear in the data, say ◊, so your data will become The Lord of the Rings◊J. R. R. Tolkien. Then run Text to Columns as specify ◊ in the Other field.
Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.

By: Aubrey Love | Updated: 2020-02-19 | Comments (11) | Related: More >T-SQL

Split Text To Columns In For Addresses Online

Problem

Typically, in SQL Server data tables and views, values such as a person's name or theiraddress is stored in either a concatenated string or as individual columns for eachpart of the whole value. For example: John Smith 123 Happy St Labor Town, CA. Thisdata could be stored in a single column with all the data, in two columns separatingthe person's name from their address or in multiple columns with a column for eachpiece of the total value of the data.

With this, DBA's are inevitably always having to concatenate or parsethe values to suit our customer's needs.

To build on our sample from above, I have an address column that has the fulladdress (street number and name, city and state) separated by commas in a concatenatedcolumn. I want to break that data into three columns (street, city and state) respectively.

Solution

For this tip, let's create a test database and test table for the parsing example. In this scenario, we have a street name and number, followed by the city,then the state.

The following code creates a table in the new (test) database with only two columns- an id column and an address column.

Populate the table with some generic data. Notice that the address is one stringvalue in one column.

Confirm the table creation and data entry with the following SQL SELECT statement.

Your results should return, as expected, two columns - the id column and theaddress column. Notice the address column is returned as a single string value inimage 1.

Breaking down the data into individual columns. The next step will be to parse outthe three individual parts of the address that are separated by a comma.

Since we didn't call the id column in the SELECT statement above, the resultsonly returned the address string divided into three columns, each column returninga portion of the address for the respective column as shown in image 2.

Here in the real world, DBA's are often faced with more complex tables or viewsand not just a simple two column table as in the above sample. Although the sampleabove is a great primer for dissecting how to parse a string value, thefollowing section demonstrates a more complex situation.

Again, create a sample table in our test database to work with. Here, we createa slightly more complex table with some additional columns.

Split Text To Columns In Numbers

Insert some generic data into the test table.

Now, let's create a mixed SELECT statement to pull all the columns that breaksdown the 'empName', 'empAddress' and 'empPhone' columns into multiple columns.

In the following block of code, notice that I restarted my 'place / position'count on each column that I want to parse. Each time you start parsing a new column, you must start your count over. You should also note that the 'empName'and 'empAddress' string values are separated with a comma while the 'empPhone' stringvalue is separated by a hyphen and thus should be reflected in your 'parse' functionbetween the first set of single quotes.

The results should return thirteen columns from the six columns queried againstas shown in image 3.

In summary, the PARSENAME function is a handy addition to your T-SQL toolkit forwriting queries involving delimited data. It allows for parsing out and returningindividual segments of a string value into separate columns. Since the PARSENAMEfunction breaks down the string, you are not obligated to return all the delimitedvalues. As in our sample above, you could have returned only the area code fromthe 'empPhone' column to filter certain area codes in your search.

Next Steps

Last Updated: 2020-02-19

About the author

Aubrey Love has been a Database Administrator for about 8 years and is currently working as a Microsoft SQL Server Business Intelligence specialist.
View all my tips