Pasting Text From Website Into Excel

[chasw67]

I am trying to copy the contents of a shopping basket from a website into an excel spreadsheet. The problem is what appears to be one line of text on the website pastes as 2 rows in Excel - so for example I copy something like this:

Robin Adnan Anders - Ornaiyo £0.30

But in Excel I get

1) Robin Adnan Anders - Ornaiyo
2) £0.30

I have tried different paste options but it always pastes as 2 rows and not one row with two columns as I would like. I want the item/name in one column and then the price in the second column so I can change the order from high to low when I have imported all the data.

I should say I will importing many lines of data not just single lines so when I try several lines of text it will always put the price in a row underneath the description. I read something about using "text to columns" but could not work it out. Also I read about "transpose" as well and I read it should be availbale when you right click and choose paste special but it wasn't there when I tried that.

If anyone could walk me through how to achieve this copy and paste request I would be very grateful.

I am using Excel for Mac 2008

[LJMetzger]

Hi Chasw67,

I am a Windows person, but if the Mac behaves in a similar manner:

You probably have one or more non-printable characters (such as Carrriage Return [ASCII decimal 13] and Linefeed [ASCII decimal 10]) in the string. If that is the case you have to remove the characters.
You should be able to remove the extra characters with the Replace() command:

Please Login or Register  to view this content.

For a list of ASCII characters and codes see:
http://www.asciitable.com/

A Macro similar to the following may help you determine what the contents of your string looks like internally:

Please Login or Register  to view this content.

Lewis

[chasw67]

Hi Lewis and many thanks for the very quick reply. Problem is I don't understand a word of what you say !! I appreciate your effort here but I am afraid I am way too much of a noob to understand what you posted. I was rather hoping there was some really easy solution to my problem, like choosing a different paste option. I don't know what strings are or macros or non-printable characters etc. Should I just give up on trying to acheive what I wanted ?

[LJMetzger]

In that case, edit the text that contains:

Please Login or Register  to view this content.

and remove everything between the 'o' and the Pounds Sterling sign. Then put a space in between and you should have the prettyprint you want.

Lewis

[chasw67]

Hi - yes that means I would have to manually edit the hundreds of lines of text I need to import into excel which was exactly what I was trying to avoid. I appreciate your time and trouble I just wish there was a simple process to paste what I see, when I copy. I am quite surprised this appears to be so difficult, I thought the whole idea of choosing paste options like text only for example would do that. I understand there is something else as well as the visible text being copied when I choose copy, I assumed the various paste options should take care of that. Obviously not hence why I had moved onto looking at altering the text once imported into Excel. From Googling I saw that "transposing" the data can turn a row into a column which seemed like a solution but all references said select transpose from the right click paste options and I don't see that when I try. Text To Columns seemed to be another option posted as a solution but I couldn't work out how you tell it to move a row up to join the one above. OK well if there is no easy to understand solution to this I will just give up but I am surprised it is so difficult to paste what you see when you copy.