Excel - Value pasted cell does not parse with Text to Columns

Question

Please see the picture below:

I did my best to label everything in the bold text!

Column F has data separated by returns (partially shown in F2 with Wrap Text).

Cell V2 uses a formula to convert the returns in F2 to commas, a tip I got from this community.

What I NEED is to be able to parse the values in V2 with Text to Columns.

In V5, the Paste Values of V2, the Text to Columns only returns the first number in V6.  I tried EVERYTHING in the wizard, trust me.

The extra frustrating part is this:  I manually typed the data in V9, identical to the Pasted Values in V5.  No spaces at the end of either.  I even Format Painted them.  Text to Columns returns EXACTLY what I want in V10 from the manually entered data.

So why won't Text to Columns work on the pasted values?????

Solving this issue would save me dozens of hours of work.  This is a small sample from an Access Database; I need to separate out the values in the F column!  Please help!

Answer 1

Hi,

Select F2:F11 and go to Data > Text to columns > Delimited > Next > Other > Ctrl+J > Next > Destination > V2 > Finish

Hope this helps.

Answer 2

Hi Tanner,

Have you tried Ashish's suggestion? Please keep us updated when you have time.

Regards,

Sheen

Answer 3

Thank you for your answer, Ashish.

Unfortunately, when I select "Other" as the delimiter and type in "Ctrl+J" nothing happens.  I have seen and attempted this solution before.

To clarify, you are saying to hold down the Ctrl key and then press "J", correct?  Doing this changes neither the data preview box underneath the delimiter selection nor the actual output when I attempt to finish the wizard.

Answer 4

Hi,

Upload your workbook to OneDrive and share the download link here.

Answer 5

Excel 2010/2013/2016 Power Query (aka Get & Transform)

No formulas, no Text to Columns, no ALT+0010.

http://www.mediafire.com/file/21nhtqfk8ff6c2g/12_27_16.xlsx