Excel "Text to column" - changing data back to one cell

I need to export data from a database I am working with, to an exce
file, to make it easier for me to edit certain changes.

For one part of this, all of the data appears in one cell (seperated b
delimeters.)

I use the "text to columns" function, to change the data into multipl
columns to make it much easier to read, work with, and edit.

My problem, is changing it back to the one cell, so that I may the
import it back to my database.  

Part of my excel sheet looks like this after the text to column:

2325			test

where the large blank space is two empty cells.  I know the correct wa
to change this back to one cell, is to use a cell and type
=A1&"_"@A2 
as an example.  this works perfectly.  however, i need to do this t
about 50 different cells.  and it is too time consuming to type thi
out.  is there a way i can select a whole bunch of things...like 
selecting a whole column for a sum, or other functions.  I know ther
is the "Concatenate" function, but I can not seem to do that an
highlight the data I want.  I'd still need to type it all.

Any suggestions???

:confused

--
Message posted from http://www.ExcelForum.com

0
6/1/2004 1:32:22 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
299 Views

Similar Articles

[PageSpeed] 9

Seth,

You may need to save the file as txt or csv (see the file types box in the
Save as dialog).  This will separate your fields with commas, which most
data base programs expect anyway.  This will probably be the best way.

If you're going to do it with formulas, copy the first formula down with the
fill handle (lower right corner).  If you have commas or quotes in your
data, you'll likely run into trouble getting the data into the data base
properly.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"sethf00 >" <<sethf00.176bzw@excelforum-nospam.com> wrote in message
news:sethf00.176bzw@excelforum-nospam.com...
> I need to export data from a database I am working with, to an excel
> file, to make it easier for me to edit certain changes.
>
> For one part of this, all of the data appears in one cell (seperated by
> delimeters.)
>
> I use the "text to columns" function, to change the data into multiple
> columns to make it much easier to read, work with, and edit.
>
> My problem, is changing it back to the one cell, so that I may then
> import it back to my database.
>
> Part of my excel sheet looks like this after the text to column:
>
> 2325 test
>
> where the large blank space is two empty cells.  I know the correct way
> to change this back to one cell, is to use a cell and type
> =A1&"_"@A2
> as an example.  this works perfectly.  however, i need to do this to
> about 50 different cells.  and it is too time consuming to type this
> out.  is there a way i can select a whole bunch of things...like
> selecting a whole column for a sum, or other functions.  I know there
> is the "Concatenate" function, but I can not seem to do that and
> highlight the data I want.  I'd still need to type it all.
>
> Any suggestions???
>
> :confused:
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nowhere1083 (630)
6/1/2004 3:16:22 PM
seth

The easiest method may be to just make a copy of the worksheet before doing
your Text to Columns.

Do not alter the copied sheet data and use it to place back into the data
base.

If you want to use a user defined function that will concatenate cells,
ignoring blank cells, here is some code.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
    For Each cell In CellBlock
        If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & " "
    Next
    ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Assuming data is in A1 and D1, in E1 enter  =ConCatRange(A1:D1) and drag/copy
down column E.

When/if happy with the results, copy Column E and paste special(in
place)>values.  Delete other columns.

Gord Dibben Excel MVP

On Tue, 1 Jun 2004 08:32:22 -0500, sethf00
<<sethf00.176bzw@excelforum-nospam.com>> wrote:

>I need to export data from a database I am working with, to an excel
>file, to make it easier for me to edit certain changes.
>
>For one part of this, all of the data appears in one cell (seperated by
>delimeters.)
>
>I use the "text to columns" function, to change the data into multiple
>columns to make it much easier to read, work with, and edit.
>
>My problem, is changing it back to the one cell, so that I may then
>import it back to my database.  
>
>Part of my excel sheet looks like this after the text to column:
>
>2325			test
>
>where the large blank space is two empty cells.  I know the correct way
>to change this back to one cell, is to use a cell and type
>=A1&"_"@A2 
>as an example.  this works perfectly.  however, i need to do this to
>about 50 different cells.  and it is too time consuming to type this
>out.  is there a way i can select a whole bunch of things...like 
>selecting a whole column for a sum, or other functions.  I know there
>is the "Concatenate" function, but I can not seem to do that and
>highlight the data I want.  I'd still need to type it all.
>
>Any suggestions???
>
>:confused:
>
>
>---
>Message posted from http://www.ExcelForum.com/

0
Gord
6/1/2004 3:36:28 PM
Reply:

Similar Artilces:

Text to Column with fixed width
I have text file with about 300 lines items that are not delimited but the width of each columns is fixed and do have the value. For example, cell 1 is char(1-2), cell 2 is char(2-14).... I want to be able to copy the whole thing in Excel, run a macro that split the string into the different cell. I can manually do it using the Text to Column function and setting the width one by one but it's time consuming since this import is occuring everyday. Can you help? Thanks, If you want to split the data into multiple columns before you send it to Excel, you could do that in a query, and expo...

data import and text to columns
I am trying to import a table, separated by delimiters, from the web. The table's data changes frequently. I can use text-to-columns to sort the table into the proper columns, but don't want to have to do this every time I refresh the data. Is there any way around this? Heres the table I'm talking about. http://polar.ncep.noaa.gov/waves/latest_run/enp.46062.bull Thank you, David David Try turning on the macro recorder while you open the web site, select all and copy then paste to an Excal sheet and do your Data>Text to Columns as pipe de-limited. Run that macro when you...

Disable Text to Column from popping up automatcally
When I open a certain down in Excel it automatically wants me to do the text to column thing but if I do that then that will mess up my Macro. I can't hit Cancel or the document won't come up, and if I double click on the lines to delete them my macro won't come up. Everytime before when I opened this download it never did this, is there a way to disable this option? -- Message posted via http://www.officekb.com ...

Text to Column
Is there any way to separate Dollar amount to another column (last five digits)? Not all figuers have a $ sign in front of the amount. Thank you USPS 0545019576 LOS ANGELES CA 9.60 USPS 0545019576 LOS ANGELES CA 9.40 USPS 0545019577 LOS ANGELES CA 4.70 SHELL OIL 57442712808 UPLAND CA 16.20 VONS FUEL 10026813 UPLAND CA 26.50 If you want the last 5 characters, use =RIGHT(A1,5) if you want it as text, or =RIGHT(A1,5) if you want it as a number. -- David Biddulph "ajyourpal" <ajyourpal@discussions.microsoft.com> wrote in message news:43E1D9BD-913D-418F-998F-...

Text to Columns 03-31-10
I have data coming in that is on two rows, I want to move the lower row to be on a single line with its corresponding data, is there a simple way to do this? Ex: 12345 June July Aug Johnson I want the "Johnson" piece of data to be on the same row. 12345 June July Aug Johnson I have hundreds of pages I am bringing in so I want to be able to change all data at once. -- KKD If every 2nd row in Column A is the cell to move up 1 row. Sub MoveEvery2ndRow() Dim lRow As Long Dim lStop As Long lStop = Cells(Rows.Count, "A...

text to column code / invalid syntax
I was trying to use this code to automatically delimit cells upo opening the workbook but it is not working. Can someone tell me what i going wrong? Sub auto_open() Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True End Sub see example below ... Thanks, Vat -- Message posted from http://www.ExcelForum.com Hi what exactly ...

complicated text to column
My imported "Name" column (column A) contains different name formats: First Name Last Name; First Name Middle Initial Last Name; and, in some cases, First Name Middle Name Last Name (for example: Mary Smith, John B. Doe, William James Burrows). Is there a simple way to convert all names to three columns with the Given Name in Column B, the Middle Initial or Name in Column C, and the last name in Column D? As your data is not Consistence - there might be a way around with formulas that brake the names into 3 parts but I would suggest that you start by examining "...

Text to column, fixed width
I have a chart based on imported data from a database. The problem is after anytime I refresh data, the chart is colasped, all data become straight line at 0. I have to click data/text to column/fixed width/finish to get my chart back. My question: How can I format the worksheet so that I don't have to do this after refresh data? Thanks Daniel You could record a macro to do the import followed by the parsing (text-to-columns) best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Daniel" <Daniel@discussions.microsoft.com> wrot...

text to columns problem #2
I have an invoice that uses "text to columns" to help it update. I use macro to paste data from a network application to the invoice. When close down Excel, even though I have it saved, it no longer separate the test when pasted. Anyone know why? Thanks, Vat -- Message posted from http://www.ExcelForum.com A guess in the absence of other replies :- Normally with a text file we have to SaveAs a .xls workbook otherwise the file remains a text file. I wonder if this is affecting your data. --- Message posted from http://www.ExcelForum.com/ ...

Text to columns #3
I am trying to split 700 names with phone numbers. They are in column A. There are hyphens between the names and phone numbers. Do I have to replace it with a coma and if so can i do the first one and copy it to do the rest or do i have to do each one individually. I not really caught on to this posting yet, so please accept my thanks in advance. Any help is very much appreciated. ie John Doe - 250-555-5555 It looks like you have name space - space number. Try this: Select the range of cells in question Goto the menu Edit>Replace Find what: <space>-<space> That is, ...

text to columns #5
I have a column of text I want to split. How would I make it split on two spaces. Example Mt Vernon IL600111 I want Mt Vernon in one column and IL600111 in the other. gls858 You could do a find and replace to change the double space to a comma and then do a <data> <text to columns> with the comma as the delimiter -- Greetings from New Zealand "gls858" <gls858@yahoo.com> wrote in message news:%23n2lTB6XIHA.4448@TK2MSFTNGP03.phx.gbl... >I have a column of text I want to split. How would I make it split on > two spaces. Example > > Mt Vernon IL6...

Text to Columns...
Does anyone know the official function of the menu option called "Text to Columns..." which is found under the "data" option on the menu bar? How does it work? What is it supposed to do? Answers to these questions would be appreciated. Thank you... Craig craig_madrin@goodyear.com It allows you to separate text in a cell or column of cells either by position (fixed) or based on a delimiter, and place the separated data in one or more columns. It can also do some rudimentary data manipulation, including parsing dates, converting numbers to text, text, etc. Th...

Text to column #5
I have a text file of data that i get on a daily basis and have to perform the 'text to column' function on each time. Is there a way that i can just place the new text data in column A and have it automatically updated starting in, say, column C. Alternatively, if that isnt possible is there a function i can write to extract the data from the text file myself. Example: Text in A1 abc 123 def 456 ghi How can i extract abc, 123, def, 456 and ghi into cells c1 through g1? If your text string is always the same length and format, one clunky solution would be to write ...

text to columns #3
I have an invoice that I cut and paste whole network application screen onto to fill out the invoice. It works really well using the text t columns feature (delimited, spaces). However, when I close the exce session, it no longer will be delimited. How do I fix this? Thanks, Vat -- Message posted from http://www.ExcelForum.com If you want to save the de-limiting, SAVE the workbook If you don't, don't. -- Don Guillett SalesAid Software donaldb@281.com "Vato Loco >" <<Vato.Loco.19fyvo@excelforum-nospam.com> wrote in message news:Vato.Loco.19fyvo@excelforum-no...

Text to Columns 02-10-10
500+ sentences, I need to seperate (text to columns) after the first "-" only. There are subsequent "-" but I only want it seperated at the first. And when I seperate it in the second column I do not want a space preceeding the text. Can you give advice on this, any help would be much appreciated! Thanks Sample Data AIR TAXI - An aircraft operator who conducts operations for hire or compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer passenger seats and a payload capacity of 7,500# or less. An air taxi operates on an on-dema...

Text to columns question
I have some data in the following format: Number firstname lastname schoolname The first three fields are one word each. Schoolname can be more than one word. I'm trying to parse the data using the data -> Text to Columns wizard, but I cannot figure out how to tell the wizard that there are only 4 space delimited columns in my data and not 5 for those instances where the schoolname is two words. Any help is appreciated. Thanks. On Jan 22, 9:50 am, MikeB <MPBr...@gmail.com> wrote: > I have some data in the following format: > > Number firstname lastname schoolname >...

Text to Columns #4
I have a list of the Fortune 1000 companies in one cell. Is there a way to convert this information into columns with: Company City State Telephone 1 Wal-Mart Stores, Inc. Bentonville, AR 479-273-4000 2 Exxon Mobil Corporation Irving, TX 972-444-1000 3 General Motors Corporation Detroit, MI 313-556-5000 4 Ford Motor Company Dearborn, MI 800-392-3673 5 General Electric Company Fairfield, CT 203-373-2211 6 ChevronTexaco Corporation San Ramon, CA 925-842-1000 7 ConocoPhillips Houston, TX 281-293-1000 8 Citigroup Inc New York, NY 212-559-1000 9 International Business Machines Corporatio...

Text to Column
:confused: Hi Everyone, I have address separated by comas. I need to separate them into columns. The max number of columns are nine(9), however not all are filled. The first four (4) are not mandatory fields. See below (the first address I have 7 comas and the second has 6 comas) Example One: 123 Room, Xie Wan Village Guangzhou Dadao Bei Road, Tonghe Town, Guangdong District, Guangzhou City, Leiming Province, PR China, 510505 Example Two 9 4th Building, 116 Street, Qing Shan District, Hainan City, Hunan Province, PR China, 430021 The Problem: When I use the Text to Column wizard; it sorts f...

Text to Columns Help
Can someone show me how to convert a column of data filled with information like this: 2.756.00-9,86259,172.00-45,953275,718.00 to 6 columns like this: 2.75 6.00 -9,862 59,172.00 -45,953 275,728.00 You could use the =MID function to chop it up, BUT, the formats of all 6 numbers would have to be the same all the time. for example =MID(a1,5,4) would give you 6.00, but that number would always have to start in the 5th position and be 4 characters long. "MarkHuether" wrote: > Can someone show me how to convert a column of data filled with information >...

Reverse text to columns
Is there any way to combine text from multiple columns into one column? Sort of like the reverse of or undoing the "text to columns" feature. I know I cn use the "&" symbol in a formula e.g. =A1&B1&C1 but this leaves a formula in the cell so if I delete the original cells it no longer works. I'd like to simply merge columns that split text into multiple columns back into one column. Thanks!! But you can simply copy>paste special>values in place to overwrite the "&" formulas to convert to values? Rick Bedard <rickbedard@min...

Text to columns #2
When using text to columns to split a date / time formatted field which is generated bya formula I have preciously had no problems. All of a sudden the data preview pain in the wizrd now shows the formula rather than the result so I cannot split. Please help!! Thanks If you are actually showing the formula then Tools, Options, Calculation, Calculation: automatic Text to Columns is really for dividing text entries, and dates are numbers so I think you are going to have somewhat inconsistent results over time with what you actually start with and what you actually end up with. ...

Text to Columns #6
Anyone out there suggest a way of splitting names. i.e Mr Anthony Lee Rodgers Mr John Edward Rourke Mr Ian Ruff Mr Ian Sanderson I am looking to get the following end result. Title 1st Name(s) Last Name Mr Anthony Lee Rodgers Mr John Edward Rourke Mr Ian Ruff Mr Ian Sanderson Regards Select the cells you want to parse and run this macro: Sub NameDropper() For Each r In Selection s = Split(r.Value, " ") r.Offset(0, 1).Value = s(0) r.Offset(0, 3).Value = s(UBound(s)) If U...

coding text to column
how do i code text to column in access vb? i need to import an excel book and i need to make sure that the 1st column is text. the numberformat = "@" does not work, as it still gives me conversion errors when importing 1. Create the table the way you want it (with Text for the field field type.) 2. Attached the Excel sheet (as a linked table.) 3. Execute an Append query to add the data to the table you set up. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot...

file names and path. How text to columns from right to left
I have an export file that is a list of file names Some times the name is 3 deep sometimes more: O:\Shared\700 Club\700 Club Information.doc O:\Shared\Accounting-IT\Invoices\Pack America\Pack America - Glen Tyler.doc I am need to get these in columns so that the file names all line up. So...I need to delimited starting from the right with the \ as the deliminator. My data is in column A. I would probably start 10 columns over pasting the first data (file name) as I do not know how deep the deepest file path is. Is there a way to do this??? Your description is not completely c...

Text to Columns Function Problem
When I use the text to columns function in Excel the column that the split data is dripped into shits up one row. Is ther a way to avoid this? I bet you misspelled "dropped" and "shifts". <vbg>. And I bet you select the whole column and the column has empty cells at the top. You can either be more specific with your selection (A3:a9999 instead of all of column A) or maybe just put some temporary values in those blank cells at the top. Actually, the only way I could reproduce this is when the usedrange of the sheet didn't include the top row(s). If I put a...