How to make SMART left/right/mid function?

[ddl8]

Hi guys,

I have a huge excel table in the following format

Time,Ask,Bid,AskVolume,BidVolume
04.06.2012 01:45:58.680,1.23932,1.2392,1.99,3
04.06.2012 07:26:15.893,1.24122,1.24113,3.99,3
04.06.2012 07:34:44.444,1.24183,1.24172,3.99,3
04.06.2012 07:47:24.503,1.24188,1.24177,5.99,3
04.06.2012 09:10:48.011,1.24169,1.24158,4.99,3
04.06.2012 09:37:59.892,1.2424,1.2423,2.99,3
04.06.2012 09:39:06.785,1.24262,1.24252,2.99,3
04.06.2012 13:04:38.143,1.24491,1.2448,1.99,3
04.06.2012 15:58:33.399,1.2496,1.24949,7.99,3
04.06.2012 09:14:52.710,1.24156,1.24144,3.98,6
04.06.2012 10:40:12.006,1.24365,1.24356,2.98,3
04.06.2012 10:40:56.069,1.24376,1.24367,5.98,3
04.06.2012 11:09:07.502,1.24354,1.24344,2.98,3
04.06.2012 13:19:48.506,1.24731,1.24723,1.98,3
04.06.2012 14:14:31.293,1.24975,1.24965,3.98,3

I need to get data for bid and ask volumes, that would be last two figures separated by comma, I cannot use right function because I would have to enter it for every single raw, Is there a way to make a left/mid function identify the first comma from the right and take the value right after it up untill next comma (in case with ask volume)?

Any help would be appreciated

Cheers,

ddl8

 

[Jaymond Flurrie]

Hi guys,

I have a huge excel table in the following format

Time,Ask,Bid,AskVolume,BidVolume
04.06.2012 01:45:58.680,1.23932,1.2392,1.99,3
04.06.2012 07:26:15.893,1.24122,1.24113,3.99,3
04.06.2012 07:34:44.444,1.24183,1.24172,3.99,3
04.06.2012 07:47:24.503,1.24188,1.24177,5.99,3
04.06.2012 09:10:48.011,1.24169,1.24158,4.99,3
04.06.2012 09:37:59.892,1.2424,1.2423,2.99,3
04.06.2012 09:39:06.785,1.24262,1.24252,2.99,3
04.06.2012 13:04:38.143,1.24491,1.2448,1.99,3
04.06.2012 15:58:33.399,1.2496,1.24949,7.99,3
04.06.2012 09:14:52.710,1.24156,1.24144,3.98,6
04.06.2012 10:40:12.006,1.24365,1.24356,2.98,3
04.06.2012 10:40:56.069,1.24376,1.24367,5.98,3
04.06.2012 11:09:07.502,1.24354,1.24344,2.98,3
04.06.2012 13:19:48.506,1.24731,1.24723,1.98,3
04.06.2012 14:14:31.293,1.24975,1.24965,3.98,3

I need to get data for bid and ask volumes, that would be last two figures separated by comma, I cannot use right function because I would have to enter it for every single raw, Is there a way to make a left/mid function identify the first comma from the right and take the value right after it up untill next comma (in case with ask volume)?

Any help would be appreciated

Cheers,

ddl8

Any chance you could use text to columns separating by comma (and space)?

 

[FormR]

Or if you want to use a formula:

Excel Workbook
	A	B	C
1	04.06.2012 01:45:58.680,1.23932,1.2392,1.99,3	1.2392	1.99
Sheet2

 

[ddl8]

Any chance you could use text to columns separating by comma (and space)?

I think you are right it would be the easiest way to do it

 

[ddl8]

Or if you want to use a formula:

Excel Workbook
	A	B	C
1	04.06.2012 01:45:58.680,1.23932,1.2392,1.99,3	1.2392	1.99
Sheet2

This is hilarious bro, works as well!!!

 

[Jaymond Flurrie]

I would use that formula too since you have one now. The problem with text-to-columns is that it takes the same time everytime you want to do it.