Splitting apart Name from Sales figures

[Guest]

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

 

[Ron Rosenfeld]

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

 

[N Harkawat]

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)))))))))