|If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.|
||Thread Tools||Display Modes|
Copy Paste From Website and Reformat
My aim is to copy the contents from an online shopping basket from a website and paste them into Excel so I can change the order of the contents by price high to low. I am using MusicMagpie to find the value of hundreds of CDs I own to see if they are worth selling. So after scanning each CD the price offered by MM is listed and some are very low offers and some higher. After scanning the hundreds of CDs I have, I want to then change the order in the shopping basket from high to low based on valueation. This is not on option on MM website so I thought I could just copy and paste into Excel and then order by highest to lowest price.
My issue is when I copy what appears to be a single line of text on the website and then paste it into Excel it pastes as 2 rows and one column so eg:
Robin Adnan Anders - Ornaiyo £0.30
Los De Abajo - Lda V The Lunatics £0.64
Nuru Kane - Sigil £0.85
ends up looking like this:
Robin Adnan Anders - Ornaiyo
Los De Abajo - Lda V The Lunatics
Nuru Kane - Sigil
OK so I understand there are hidden characters or "something else" which is adding a paragraph break between the description and the price.
I have posted my query on another forum specifically for Excel and the responses I got suggested -
You should be able to remove the extra characters with the Replace() command:'Replace lower case 'c' with upper case 'X'
sText = Replace(sText, Chr(99), "X")
A Macro similar to the following may help you determine what the contents of your string looks like internally:Sub DisplayTextStringCharactersAsIntegers()
'This is used to view numerical values of each character in a string
'Values less than 32 are generally NON-PRINTING characters
'Carriage Return / Linefeed is replaced by a Tilde (~)
Dim i As Long
Dim c As String
Dim sText As String
sText = Range("C3").Value
sText = Replace(sText, vbCrLf, "~")
For i = 1 To Len(sText)
c = Mid(sText, i, 1)
Debug.Print i, Asc(c), c
Problem is I have no idea what any of that means as I have only ever used Excel by manually inputting data - I have never run replace commands or run formulas or macros and when I tried to find out how I was bamboozled.
The one thing I did understand a little bit of was Text To Columns and I can see I could sort the order of a single line of text into 2 columns - one for the description and one for the price. However the problem remains how the get the price onto the same line as the description.
So I thought try pasting the shopping basket details to Word and try all the paste options - nothing got the price to jump up with the description. So I thought well I could try a simple replace process by finding the £ sign and replacing with x2 backspace commands and that should "push" the price back up to the line with the description. Didn't work !
I am at a total loss now as to how to acheive my aim. I thought it should be a fairly straightforward affair but I just can't solve it.
Perhaps I am going about this all wrong and/or there is a very simple solution and so I welcome any advice on how I may acheive my aim.
I think from all this description you can assess I am a "light" user of Word and Excel but I do use them but just can't work out the easiest approach to getting my shopping basket contents into Excel. I posted here because I feel if I sort the formatting option first in Word that may be easiest but if I am wrong let me know.
Thanks in advance for any advice.