Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Splitting apart Name from Sales figures
Hi,
I was sent several pdf files that I want to bring into Excel and then import into into MS Access and run some queries. I first tried to bring it into Excel by copying and pasting the data from the PDF to Excel. The data consists of company name and then amount which is in British Pounds. There is no symbol with the data. When I paste it into Excel everything on each line goes into one cell. So it may say ABC Corporation 456.23 on one line and then CBA Limited Liability Corp. 223.43 on another. I want to split the data apart so that the company appears in one cell and the amount in another. That way I can import it into Access and have two different fields of data. Thanks, Chuck -- Chuck W |
#2
|
|||
|
|||
On Wed, 1 Jun 2005 06:55:04 -0700, ChuckW wrote:
Hi, I was sent several pdf files that I want to bring into Excel and then import into into MS Access and run some queries. I first tried to bring it into Excel by copying and pasting the data from the PDF to Excel. The data consists of company name and then amount which is in British Pounds. There is no symbol with the data. When I paste it into Excel everything on each line goes into one cell. So it may say ABC Corporation 456.23 on one line and then CBA Limited Liability Corp. 223.43 on another. I want to split the data apart so that the company appears in one cell and the amount in another. That way I can import it into Access and have two different fields of data. Thanks, Chuck If, as in your example, the amount is always the last item in the data (and preceded by a <space), then, Company Name: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Amount: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) --ron |
#3
|
|||
|
|||
if your amount is always last item of the data whther separated by space or
not you could use this for text part use =LEFT(A1,LEN(A1)-SUMPRODUCT(--(ISNUMBER(-RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1)))))))) for amoiunt part =--(RIGHT(A1,SUMPRODUCT(--(ISNUMBER(-RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))))))) "Ron Rosenfeld" wrote in message ... On Wed, 1 Jun 2005 06:55:04 -0700, ChuckW wrote: Hi, I was sent several pdf files that I want to bring into Excel and then import into into MS Access and run some queries. I first tried to bring it into Excel by copying and pasting the data from the PDF to Excel. The data consists of company name and then amount which is in British Pounds. There is no symbol with the data. When I paste it into Excel everything on each line goes into one cell. So it may say ABC Corporation 456.23 on one line and then CBA Limited Liability Corp. 223.43 on another. I want to split the data apart so that the company appears in one cell and the amount in another. That way I can import it into Access and have two different fields of data. Thanks, Chuck If, as in your example, the amount is always the last item in the data (and preceded by a <space), then, Company Name: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Amount: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a daily sales sheet to pull from monthly figures sheet | Excel Worksheet Functions | |||
Trying to create a Sales Projection/Forecast/Estimate | Excel Discussion (Misc queries) | |||
Calculate minus figures only | Excel Discussion (Misc queries) | |||
Need help producing simple sales figures | Excel Discussion (Misc queries) | |||
Help with sales chart | Charts and Charting in Excel |