Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

EXCEL paste formula into cell after having Parsed on Commas causes formula to break into parts 1

Status
Not open for further replies.

appelq

IS-IT--Management
Dec 28, 2004
72
US
In Excel 2010
I pasted data from another source that was Comma separated values (not a CSV FILE)
I then did the DATA / Text to Columns function; using Delimited, on Commas
that worked fine.

I then inserted a column and pasted a simple formula =IF(G2>0,LEFT(G2,6),"") <-- Note this has 3 commas in it..

When I click into a cell and paste the formula, it breaks into 4 parts [=IF(G2>0] [LEFT(G2] [6)] ["")]
And spreads across 4 cells (overwriting the data in the other cells.

It works OK, if instead of pasting the formula directly into the cell, I past it into the formula bar.

But I have to do this a lot and invariable forget.

This ONLY happens in workbooks that I first parsed on commas.

Is there a way to fix that?
 
Hi,

Here's how you can avoid this anomaly.
[tt]
1) DOUBLE-CLICK in the empty cell you want this formula in.

2) ctrl+v to paste

3) hit ENTER
[/tt]

Alternatively, you could
1) select a single cell with data,
2) activate the Text to Columns wizard and
3) uncheck each parsing delimiter.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
It's one of annoying excel features. After applying text to columns I restart excel if I work with important data.

combo
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top