Solve : Excel Data Entry?

1.	Solve : Excel Data Entry?
Answer» I would like to enter a large number of nine to seventeen digit numbers that I have copied from a website into an Excel spreadsheet. They are currently separated by commas. I would like to have each number in a separate cell. How would I accomplish this? A PDF document with the numbers is attached [attachment deleted by admin to conserve space]Large document. Here is part of it: 3","100000857240769-0","746858137-3","746858137- 0","100002102230952-0","100002102230952-3","100000118202437- 3","100000118202437-0","100000748429788-3","100000748429788- 0","1699007105-3","1699007105-0","1487529605-0","1487529605- It does not seem to be a standard number format. What does the dash (-) mean? In regular expression it would mean subtraction. Is that right? If all the dashes could be converted to periods (.) then excel could read it as a list of numbers. But as it is, it can only be a string, not a number And the end of line char is in the wrong place. You can export the PDF as a plain text file and then the spreadsheet program, Excel, can read into a spreadsheet. Bu the dash char is going to give unexpected results. This might help. Import or export text (.txt or .csv) files That is the best I can do. Actually the dash and number following need to be removed from the entries. My next question was going to be if there was a simple way to do that once the numbers are in cells. I have them in a text file, just converted it to pdf to attach here.You need to remove unwanted chars and fix the en of line points. A suitable tool for your use would be Notepad++. https://notepad-plus-plus.org/download/v6.8.6.html Download Notepad++ Documentation is also there. Notepad++ can remove line breaks and remove unwanted chars from text. It can insert end of line a certain points. You might be able to put it in a batch file. I have yet to try that. How many columns do you want? Excel needs the end of line char to start the next row. Quote from: Geek-9pm on November 18, 2015, 11:48:36 AM You need to remove unwanted chars and fix the end of line points. What unwanted characters? He said nothing about unwanted characters. Quote from: Geek-9pm on November 18, 2015, 11:48:36 AM Excel needs the end of line char to start the next row. No, it does not. Excel can parse data by using the Text-to-Columns command. Numerous references on parsing data with Excel can be found by searching on a phrase such as how to parse data in excel. Here's one reference: http://www.addictivetips.com/microsoft-office/excel-2010-split-column-data-into-two-parse-cells/ Although it DEMONSTRATES parsing into two columns, more columns can be easily done using the same procedure. I see one variation in the data shown in your PDF file. The first row begins with a " followed by the first long number. The last char in that row is a - . The next row begins with 2, and then all subsequent rows start with a single digit number. In other words, I get the impression all rows should end in a single digit number following the last - in the group of numbers and then a " as the last char of every line. Actually, all the QUOTATION marks are not needed when parsing in Excel. So, removing them from the file before STARTING the parsing procedure would work best, I believe. To remove the quotation marks, you could open the file in WordPad and use the REPLACE command to replace them with nothing. Then, save the file and import it into Excel, invoking the Text-to-Columns command during the import, or copy the corrected data in WordPad and paste in to Excel as you simultaneously parse. Actually there are unwanted char. The dash and following numbers needed to be removed. I didn't mention it until the subject of the dash being interpreted as a minus came up. Thanks you though for the Text-to-Column parsing info and link. They were HELPFUL Quote from: trusky on November 20, 2015, 09:51:05 AM Actually there are unwanted char. The dash and following numbers needed to be removed. I believe you could go through the parsing procedure and have Excel treat the dashes as a delimiter. Thereby, you could get the dashes and single digit number after them in a separate column, or perhaps two columns with one containing only the dash and one containing the single digit number. Then, you could just delete those columns since they are not needed.

Answer»

I would like to enter a large number of nine to seventeen digit numbers that I have copied from a website into an Excel spreadsheet. They are currently separated by commas. I would like to have each number in a separate cell. How would I accomplish this? A PDF document with the numbers is attached

[attachment deleted by admin to conserve space]Large document.
Here is part of it:
3","100000857240769-0","746858137-3","746858137-
0","100002102230952-0","100002102230952-3","100000118202437-
3","100000118202437-0","100000748429788-3","100000748429788-
0","1699007105-3","1699007105-0","1487529605-0","1487529605-

It does not seem to be a standard number format. What does the dash (-) mean? In regular expression it would mean subtraction. Is that right?
If all the dashes could be converted to periods (.) then excel could read it as a list of numbers.
But as it is, it can only be a string, not a number And the end of line char is in the wrong place.
You can export the PDF as a plain text file and then the spreadsheet program, Excel, can read into a spreadsheet. Bu the dash char is going to give unexpected results.
This might help.
Import or export text (.txt or .csv) files
That is the best I can do.
Actually the dash and number following need to be removed from the entries. My next question was going to be if there was a simple way to do that once the numbers are in cells. I have them in a text file, just converted it to pdf to attach here.You need to remove unwanted chars and fix the en of line points.
A suitable tool for your use would be Notepad++.
https://notepad-plus-plus.org/download/v6.8.6.html
Download Notepad++
Documentation is also there.
Notepad++ can remove line breaks and remove unwanted chars from text. It can insert end of line a certain points. You might be able to put it in a batch file. I have yet to try that.
How many columns do you want? Excel needs the end of line char to start
the next row.
Quote from: Geek-9pm on November 18, 2015, 11:48:36 AM

You need to remove unwanted chars and fix the end of line points.

What unwanted characters? He said nothing about unwanted characters.

Quote from: Geek-9pm on November 18, 2015, 11:48:36 AM

Excel needs the end of line char to start the next row.

No, it does not.

Excel can parse data by using the Text-to-Columns command. Numerous references on parsing data with Excel can be found by searching on a phrase such as how to parse data in excel. Here's one reference: http://www.addictivetips.com/microsoft-office/excel-2010-split-column-data-into-two-parse-cells/ Although it DEMONSTRATES parsing into two columns, more columns can be easily done using the same procedure.

I see one variation in the data shown in your PDF file. The first row begins with a " followed by the first long number. The last char in that row is a - . The next row begins with 2, and then all subsequent rows start with a single digit number. In other words, I get the impression all rows should end in a single digit number following the last - in the group of numbers and then a " as the last char of every line.

Actually, all the QUOTATION marks are not needed when parsing in Excel. So, removing them from the file before STARTING the parsing procedure would work best, I believe. To remove the quotation marks, you could open the file in WordPad and use the REPLACE command to replace them with nothing. Then, save the file and import it into Excel, invoking the Text-to-Columns command during the import, or copy the corrected data in WordPad and paste in to Excel as you simultaneously parse. Actually there are unwanted char. The dash and following numbers needed to be removed. I didn't mention it until the subject of the dash being interpreted as a minus came up. Thanks you though for the Text-to-Column parsing info and link. They were HELPFUL Quote from: trusky on November 20, 2015, 09:51:05 AM

Actually there are unwanted char. The dash and following numbers needed to be removed.

I believe you could go through the parsing procedure and have Excel treat the dashes as a delimiter. Thereby, you could get the dashes and single digit number after them in a separate column, or perhaps two columns with one containing only the dash and one containing the single digit number. Then, you could just delete those columns since they are not needed.

Solve : Excel Data Entry?

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment