Excel problem with columns

[Splitlink]

I'd be grateful if someone would help me get my Sharescope price data to enter Excel in separate columns.

I get the day's date in one column and the month in a separate column but the year and price are in the third column and they are separated by a comma, like this

08,5567.00

I note that the decimal on the price has a full stop, which should not be.

Regards

Split

 

[Mathemagician]

Can you post a sample line or lines from the file?

jj

 

[Splitlink]

Yeah, with some delay!

I use a Spanish Excel, BTW. Sharescope Help does not think that there should be any difference but I know that there is with the functions, which separates with a ";", instead of a ","

However, I think that this problem is ignorance on my part, rather than a difference in the software. I know that that stop in the decimals should be a comma.

Regards Split

 

[Mathemagician]

Can you post the raw text? That is the most efficient way from point A to point B.

jj

P.S. Have you tried selecting only the column with all the 95, in it and doing Data/TextToColumns/Delimited/Comma? That might well do the trick for you.

 

[forker]

simple solution.. open it in notepad and on the file menu you will see a replace option. just enter replace . with , and select replace all .. if you want to remove it just enter replace . with and leave the second one blank.. job done just save the file and open it with xls

 

[Vrothdar]

If the other two solutions don't work you could use the LEFT and MID functions to separate out the data you need then do a copy > paste special > values to sort it out.

If you put this formula into cell E1 =LEFT(C1,2)
and this one into cell F1 =MID(C1,4,7)

Drag those down as far as you need then select all you've just created then select C1 and copy > paste special > values to get the year and price in columns C and D. You'll then need to select all you've just copied and in the little box that pops up saying that you've got a number stored as text ask it to "convert to number" et voilla.

Of course you'll still have a . instead of a , as your decimal so you'll need to do a find and replace to clear that one up.

 

[Splitlink]

It doesn't look too good

I'm glad it isn't a matter of life or death otherwiase I'd be right up the creek.

Anyway, thanks a lot. I'll peg away at it.

Regards Split

 

[swandro]

It doesn't look too good

I'm glad it isn't a matter of life or death otherwiase I'd be right up the creek.

Anyway, thanks a lot. I'll peg away at it.

Regards Split

Say you have 95,4808.57 in cell C13, use this formula to get the 95

=LEFT(C13,FIND(",",C13)-1)

Use this formula to get the rest

=RIGHT(C13,LEN(C13)-FIND(",",C13))

If you get funny results, look out for leading or trailing spaces. If they exist, change every C13 into TRIM(C13)

Hope this helps

 

[Splitlink]

Thanks a lot for the advice, you guys. I've decided to pump in a couple of weeks data into Excel by hand and add as I go along. I can handle the formulae, etc. easily enough but all these functions, except add, average and similar simple ones, drive me up the wall.

Take care and thanks again

Split

 

[anley]

If you want to get better with Excel try this site -

Ask Mr. Excel - Tips and Solutions for Excel

I think the book is free in PDF and also he's got some tricks to turn Excel 2007 back into the look and feel of the older version.

 

[Splitlink]

If you want to get better with Excel try this site -

Ask Mr. Excel - Tips and Solutions for Excel

I think the book is free in PDF and also he's got some tricks to turn Excel 2007 back into the look and feel of the older version.

My problem is that, before the net started, I used Smart Suite, which had Lotus 123.

I pumped in the data from the FT and was, pretty well, on top of most of the stuff that I wanted to do, including macros.

Then along came Microsoft, Smart suite disappeared and, being a lot older, I simply can't be bothered to go into Excel in the same way. Besides, this problem that I had, here, dealt with exporting data which is new to me. I've decided to spend the time with my trading and use only the simplest routines.

However, I do get interested, from time to time, in how to do things so thanks for the link.

Split

 

[north5]

Split,

If you want to send me the data I can have a go at importing it to excel for you if you like. PM if you do.

 

[Splitlink]

No thanks, I solved my problem another way. Thanks for offering, though.

 

[Lucky_81]

This might be slightly late, but cant you do a simple text to columns using the , as seperator (Data - Text to Columns) then just do a ctrl-f find and replace all "." with "," shouldnt take more than a couple of mins to do an entire sheet.