Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Paste And Convert From Delimited Values

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'd really appreciate some help on the following.

I'm cutting columns of values delimited by spaces from a console

I'd like to paste the values into excel and at the same time have Excel
place values from each column into separate cells, using the spaces to
delimit the columns.

Currently, the paste special only shows a paste as text options which
places a full row into a single cell.

Currently I can only achieve my result by:
pasting data into text file
opening text file in excel
engaging the conversion options at that stage

Thanks in advance,

View Answers     

Similar Excel Tutorials

Make Negative Numbers Positive in Excel and Vice Versa
I will show you a few ways to change negative numbers to positive numbers and back again in Excel. Don't forget to ...
Replace Formulas with Values (For The Entire Workbook)
This macro will convert every formula in an entire workbook into its respective value. This is basically a quick w ...
Quickly Convert Formulas into Their Output Values in Excel
This tutorial teaches you how to convert a formula or function into its displayed output in Excel. This is very im ...
Split Text into Multiple Cells in Excel
How to split text from one cell into multiple cells quickly and easily in Excel.  This includes how to split names, ...

Helpful Excel Macros

Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
- This Excel macro filters data in Excel to display results that contain 1 of 2 possible values. This macro uses the xlor
Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra

Similar Topics

Excel file has 16 columns
row 6032 has 16 values
row 6033 has 14 values
row 6034 has 15 values

when i save the file as a tab delimited text file ( or even when i just copy the rows and paste into text file), this happens (showing last 4 columns:13-16)

6032.a b c d
6033.a d
6034.a b c

problem is- in all rows after 6032 there is one less tab character in each line

Ex: in row 6033, after 'd', there is no tab (\t), instead just newline (\n)
similary in 6034, after 'c', it's \n
this happens irrespective of how many values in the row - exactly one tab less at the end..

has anyone encountered this before ?

Note: original data has been modified to protect privacy

[EDIT] : The spaces between the columns are actually tabs and there are 2 tabs between 'a' & 'd' in row 6033 (forums don't like to show extra spaces)

I use Microsoft Office Professional 2003, and I have noticed a strange behaviour of Excel.
I have to copy some cells from an Excel file to another one, and I have to use the Paste Special function, because I want to past only the values of the cells (not the formulas or the borders). So, when I click on "Paste Special", in the options following window I select "Values" and click Ok.
Sometimes it happens that, when I click on "Paste Special", insted of the classic Paste Special options window (where I can select "Values" as paste type), another window opens, in which I can choose if the text must be pasted as formatted unicode or not formatted.
Do you know why? I have done hundreds of tests, but I didn't understand the reason of that...

Thank you very much

I am trying to paste a group of cells from one excel book that gets information from two other books. For the past...forever...when I paste into the last book, I right click the cell and paste special and got several options to choose from, the paste with values is the one I am after.

Now, for a reason I am sure is my fault, when I paste special, I get two options;

Paste link:

I don't want those options. I want the option list that gives me the option of pasting special with values. Anyone have an idea of what I could have done wrong to make the values option screen go away.

Thank you

Hello Excel Experts,

I have been trying this a number of ways and I can not find a way (if it's possible) to make it work.

I have a | delimited text- i.e. 1|2|3|4|5|6

and I would like to take this text and paste it into a cell, A1

Upon pasting it into a cell (if macro is used, that is fine) I would like to remove the | delimiter, and have it paste the values in cell B1:B6

So, to sum up:
Cell A1= 1|2|3|4|5|6
when cell A1 contains the data (1|2|3|4|5|6)
B1 = 1
B2 = 2
B3 = 3
B4 = 4
B5 = 5
B6 = 6

Thanks in adavance!

Hi there we have a spreadsheet provided by work into which we paste tracking data which is a single line of data seperated by commas.

This line of text is paste specialed as text into a column of cells and the cells to the right of that column then split out the data into the required granular level breakdown. When I am pasting special as text Excel appears to be trying to paste it out into delimited fields automatically and either wont let me paste over the protected cells in the columns to the right or when unprotected it overwrites them.

Is there a setting in Excel covering pasting protocol?

Example of the data pulled from tracking file
8TCE03078315W,Received into,,HITCHIN DEPOT,12-nov-2010 02:20 (Production) 8TCE03078315Z,Loaded onto,,HITCHIN VAN,12-nov-2010 07:53 (Production)
I need that to paste together as one line of data into a single column

I'm using a data verification that requires using the paste special command. When I first got this thing set up, I would select Paste Special then values (radio button on the right side of the paste special window), but NOW when I use paste special, it comes up with 3 options in a box: 1.) HTML, 2.) Unicode Text, and 3.) Text. How did the paste special box get changed and can/how do I change it back? Any help would be appreciated. Thanks

Running Office2003 on WinXPpro. Application requires use of PASTE SPECIAL-VALUES.
Suddenly, for no apparent reason, when PASTE SPECIAL is selected the options box is totally different with only 2 options presented:
1) unicode text,
2) text

How do I restore the original options, i.e. the ones that include VALUES, etc.?

Have had no success discussing with other experienced EXCEL users and searching forums such as this one.

Local IT Support suggests reinstalling Office. Is this the only way?
Does this make sense to anyone?

Thanks in advance for any help,
Jim Finch


I am completely new to excel programming. I have a SQL DTS which
creates an excel file and exports data into it. The created excel file
could have variable number of work sheets.

Text data in the excel file had a leading single quote character and
spaces before the data. I can get rid of these by copying entire sheet
and paste special--values into a new sheet.

Would some one tell me how to automate to create a new excel file and
copy and paste special (with values option) all the work sheets into
the new file? I appreciate any help.

Hi there,

I'm receiving a text file regularly (that cannot be changed) that i need to import into Excel.

It seems to be 'space delimited' without text qualifiers, but the spaces are randomly delimited. Eg field 1 has one space, field 2 has 6 spaces, filed 3 has 2 spaces and so on.

I would normally use the 'text to columns' function with 'space delimited', and 'treat consecutive delimiters as one' to sort this out, but problematically some fields have text in them with spaces in it without text qualifiers (eg field one, row 1 has something like 'red box'; field one, row 2 might have 'table') - this results in field one row 1 appearing as 2 fields when it is converted.

Can anyone help?

I know there is a quick solution - as someone did the conversion quickly and for a range of reasons didnt explain it and for a whole set of other reasons, im a little embarrased to ask....

any thoughts?

I want them to be able to copy and paste cell values without it also copying and pasting the validation. This is because I have different validation settings for every single cell, and don't want to change those everytime I copy and paste. I know that you can use paste special to paste values without validations, but that will get tedious, and it doesn't seem to work anyway - when I paste special the value then its as if the paste special overrides whatever validation criteria I was using. Is there a setting or a trick to be able to paste values only all the time without using paste special, AND when I paste values only I dont want it to override the validation I have set? For example, if I have validation set for only allowing numbers between 0 and 2 and input 54 manually an error message occurs of course, but if I copy and paste special the number 54 into the cell it then allows that to happen despite setting validation to only allow numbers between 0 and 2, and despite pasting special values only.

Hey Guys, First time post, Not sure what is going on, Happens all the time but usually just drop it and try something else...

I am trying to copy a range of cells and Need to transpose the values. Easy Enough? When I right-click and paste special, it gives me a new window with an array of options but not the standard Paste special value screen. I have used the others in the past and just pasted text. However this does not give me the paste transpose option which is what I need.

I am sure this is a User error where there is something about the way that I am copying this information but I am Drawing a Blank... Please help guys


I want to save an excel xls file as the format of "formatted text
(space delimited)".
My question is given the excel file (from which, i know the width of
each column, and the value of each range), how do I know the number of
spaces between any two neighboring values in the saved text file? (I
want to be very specific about that number) For example, if in the
original excel file, range("A1") = "0.123" and range("B1") = "abc",
what would be the number of spaces between the "0.123" and "abc" in
the text file?

For column width I can get in vba (columns(1).width), but I guess the
number of spaces apparently also depends on the width of the specific
content within a cell. For example, for a cell with the content
"0.123" or "abc", the width of the content could be (the width of a
single digit * 4 + the width of a decimal point) or (the width of a
letter * 3). It should also depend on the font/format/size of that
cell. After I know the width of the column and the width of the
content, the number of spaces between neighboring values should be a
function of the difference in the widths in excel. But I can't find
any websites/books which would give me this kind of information.

Thanks a lot, greatly appreciate any help or tips.

Just to clarify, I am more interested in the answer to my question
above, rather than a workaround such as saving the file first, then
manually increase/decrease the spaces in the text file.

Hi, I have excel 2007, I am a somewhat advanced user...

Question in short
i have a single excel file that wont let me copy cells and paste them to a new excel file...why

More details
only this one excel file wont let me paste cells to a new excel file. All of my other excel files i can copy and paste between them/ to new ones freely. Of interesting note, the very first time I try to copy a cell from the crappy file to a new one it works! every time after that it will paste an empty cell. If i choose paste special I don't get the usual options...instead my choices are (Microsoft office excel worksheet object, picture, bitmap, microsoft office excel 2007 binary format, microsoft excel 3.0 format, Biff5, SYLK, DIF, XML spreadsheet, HTML, unicode text, Text, CSV, Hyperlink) where as normally my options are contained within a 'paste special box'....

Any ideals? please help!!!!

and thank you for your time.

I use the Import Text Wizard to paste comma delimited text into an Excel
spread sheet. I end up with a single row of multiple columns. One column for
each piece of delimited text. What I really want is delimited text in a
single column of mutliple rows. One row for each piece of delimited text. How
can this be done?

Having problems getting COUNTIF to recognize text from a paste special values.

Here's what I'm trying to do:

Column A has two words (e.g. mister excel)
Column C has a formula to reverse the order of the words (outputting e.g. excel mister)

I then copy and paste special values of column C to Column B where I have conditional formatting to highlight duplicate values between Column A and Column B.

When I use paste special values, duplicates from columns A and B never highlight. If, however, I manually type in the same word that has been paste specialed, the highlight will appear.

I've tried formatting the cells but that is not working either.

This is perplexing because I've never run into this problem before. But it's so simple I must be overlooking something obvious. An options setting that got tweaked by mistake?

Anyway, I've got two Excel windows open. In window 1, I've got a variety of data and formulas.

- I highlight the array
- copy
- move to window 2
- paste

But the formulas don't get pasted. It only pastes the text result of the formulas along with all the other data, of course.

I'm not doing a "paste special" and the paste icon drop down shows the Formulas, No Borders, Values, and Transpose option grayed out.

Any ideas?

If I select a whole cell and paste it normally, I do ok, but if I have copied anything else either in Excel or outside it and then paste, it behaves like a text-to-columns command. I tried pasting special/values and that doesn't help. I actually did a text-to-columns exercise on purpose yesterday, and this has only been happening since then. I feel like I am stuck in text-to-columns mode. What gives?

I'm trying to do a copy and paste (special - values only) between 2
workbooks. I select the cells in one and click the copy button (also have
tried Ctrl-c), but when I switch to the other WB and select the arrow beside
the Paste button, the top options (Formulas, Values, etc) are grayed out. If
I select Paste Special (the bottom option), it gives me a box asking if I
want to paste the data as a Excel Worksheet Object, Picture, Bitmap, ...

If I try to paste in the same workbook (where I am copying from), the
Formulas, Values, etc. options are available.

I have each workbook open in separate Excel sessions - I assume this may be
causing the problem. But why does it do that?

Bill @ UAMS

Hi, I have a problem sometimes with the paste special options when going from one excel workbook to another. For example, sometimes when I copy data from one workbook and then paste special into another, I get the options that include: All, formulas, values, formats, has the option to transpose the data and paste link among other options. And then sometimes I try and paste data to another workbook and I end up the paste special options: Bitmap Image Object, picture, bitmap, and I also lose the ability to paste link. Well you can do it, but it puts it in as an object.

What I want is the first paste special option I described. Is there a setting to change? The data is nothing special I'm usually copying over, just normal excel entries.

Thanks in advance.

After doing a Text to Columns operation it seems that everything you paste from outside gets the same parsing treatment. Thus when I paste external data into a single cell, Excel is rudely spreading it over several cells instead of just the one. Simple example: type a b c in notepad (note 2 embedded spaces) and copy the 5 characters and paste in cell A1. Do data/text/delimited and check "space" as delimiter, and finish. If I described it correctly, A1..C1 will contain 1 letter each.

Now go back to notepad, copy it, and paste to cell D4. Does it spread across 3 cells for you without asking, as I'm getting in XL03?

What I want is a way to turn off the automatic parsing, if anyone knows how. Restarting Excel is the best I've got so far.

(Did I say "rudely" before? Oh, I meant to say "courteously." I'm so grateful when Microsoft makes my decisions for me! After all, the world is nothing but cattle and level 1 support material "We don't care! We don't have to! We're the phone company! Snort!"

Can anyone show or direct me to a simple macro for a simpleton like to convert ASCII column values into separate column for each value? Say I copy/paste columns from and ASCII/txt file into an Excel worksheet in the column A. All the values go into this one column, so everytime I need to go through the tedious text to column option in Excel to put the values into separate cells. It can get pretty tiresome and time-consuming if I have to repeat this a lot - which I do at the moment. I just need a macro or a template that splits the tab or space delimted ASCII columns to separate Excel columns everytime I paste them into the first column (col A).

Paste can this be set to default to Paste Special Values only ?
I have a sheet with a number of lists validated drop down boxes. The sheet is networked and works fine.
Problem other users as they are entering information into the required cells they are copying and pasting. Then the inevitable occurs they paste data into the wrong cells the validation from the original cell is pasted as well. Is there a way I can set paste special values only to be the default for the full sheet.
Unfortunately we use excel 97 in the office.

Thanks for any replies


I receive an e-mail generated from an old legacy system that uses semicolons as delimiters. When I paste the data into Excel, each line ends up going into one cell. I am wanting each value in between the delimiters to go into separate cells.

I know that when one uses tabs as delimiters, they end up going into different cells when you paste them into Excel. Unfortunately, it is not possible for tabs to be used as delimiters in this legacy application. Are there any other characters that can be used as delimiters that will allow someone to simply paste the values into Excel and have them entered into separate cells versus all into one? I have tried commas and semicolons, but continue to have all the data end up in a single cell. Is there a way to change the default delimiter from tabs to something else?

I understand that I could save the data into a text file and then import it into Excel, but was hoping that I could simply cut-and-paste. I also know that I can use things like the left function or the mid-function to separate the pasted values into different cells but again I'm wanting to do it simply.

Any other suggestions are greatly welcomed.

Thanks in advance for any help you might be able to offer.


I'm pretty new at Excel, but I'm trying to paste my formulas only and for some reason it is pasting the values as well. It seems like this should be very simple.

Here is what I'm doing: I highlight my cells and copy. I highlight the cells where I want to paste, click Paste Special, then the Formulas bullet. Should be easy right? I don't know what I'm missing.

A simplified description of my data:
In a four columns by four row area, A1, A3, C1, and C3 contain data I have entered. The rest of the cells are formulas. I want to paste the formulas from A2/A4 and all of column B over to C2/C4 and column D respectively. When I do paste-special, Excel replaces my values in C1 and C3 with the values from A1 and A3.

Thanks for your help!


I have my Outlook 2003 set for Rich Text with word as the editor - as recommended by Microsoft. However, when I copy a range of cells from excel 2003 and try to paste them in outlook, the data is pasted as unformatted text. All formatting from excel is lost. What am I doing wrong?

I tried to use HTML format, same problem. The paste special window in outlook shows only 2 options unformatted text or unformatted unicode text. There is no option for "paste as excel object". I am sure I am missing something in my set up.

This is how the paste looks in outlook -

1 Test1 20
2 Test2 20
3 Test3 20
4 Test4xxxxxxx 20
5 Test5yyyyyyyyyy 20
6 Test6bbbbbb 20
7 Test7 20
8 Test8 20

Can someone shed some light on this issue. Thanks for your time.