Removing Commas in between quotation mark and numbers Excel Macro

Lekha mohanty · Oct 26, 2021

In my excel sheet, I have data like

"","2,878","-51","58","202.89","3,935.80","40.65","50","3,910.95","3,927.95","50","14,250.00","5,050","0.40","0.45","7,400","0.10","0.40","106.60","2,665","-28","8,595",""

I want to delete the comma within the number, for example in the above example the value 2878 is mentioned as 2,878, here I want to delete the comma between 2878, and the other commas in the should be there. Only the comma’s within the quotation marks should be deleted.

Result should be like this.
"","2878","-51","58","202.89","3935.80","40.65","50","3910.95","3927.95","50","14250.00","5050","0.40","0.45","7400","0.10","0.40","106.60","2665","-28","8595",""

Extract_261021_112409.csv

A

1

"CALLS","","PUTS" " ","OI ","Chng in OI ","Volume ","IV ","LTP ","Chng ","Bid Qty ","Bid Price ","Ask Price ","Ask Qty ","Strike Price ","Bid Qty ","Bid Price ","Ask Price ","Ask Qty ","Chng ","LTP ","IV ","Volume ","Chng in OI ","OI "," " "","2,878","-51","58","202.89","3,935.80","40.65","50","3,910.95","3,927.95","50","14,250.00","5,050","0.40","0.45","7,400","0.10","0.40","106.60","2,665","-28","8,595","" "","173","-2","2","239.75","3,950.00","90.00","400","3,798.35","3,888.50","50","14,300.00","1,100","0.40","0.45","1,000","-","0.40","105.16","242","-62","855","" "","-","-","-","-","-","-","850","3,706.50","3,917.65","750","14,350.00","50","0.60","0.65","1,800","-0.05","0.70","108.93","39","17","73","" "","2","-","-","-","-","-","350","3,662.75","3,864.95","1,800","14,400.00","250","0.45","0.55","1,500","-0.05","0.45","103.33","59","-","209","" "","-","-","-","-","-","-","1,000","3,599.25","3,820.05","550","14,450.00","1,500","0.05","1.70","1,500","-","-","-","-","-","-","" "","4,425","-98","136","155.59","3,673.20","22.40","100","3,668.50","3,681.40","150","14,500.00","9,000","0.45","0.50","2,400","0.10","0.45","101.38","984","-200","10,897","" "","-","-","-","-","-","-","400","3,504.70","3,720.95","550","14,550.00","1,500","0.05","2.05","1,500","-","-","-","-","-","-","" "","47","-","-","-","-","-","700","3,451.70","3,665.80","500","14,600.00","450","0.55","0.60","1,500","-0.10","0.60","101.52","72","38","301","" "","-","-","-","-","-","-","600","3,406.30","3,621.30","1,800","14,650.00","1,500","0.15","2.10","1,500","-","-","-","-","-","-","" "","9","-","-","-","-","-","550","3,296.85","3,565.70","500","14,700.00","1,500","0.35","0.50","1,650","-","0.45","94.77","1","-","331","" "","-","-","-","-","-","-","700","3,309.30","3,515.70","750","14,750.00","1,500","0.05","2.10","1,800","-","-","-","-","-","-","" "","138","-1","1","236.32","3,504.25","164.25","1,000","3,282.55","3,457.00","50","14,800.00","150","0.55","0.75","200","-0.10","0.55","93.58","33","-21","18","" "","-","-","-","-","-","-","550","3,185.10","3,505.70","1,650","14,850.00","-","-","2.25","1,800","-","-","-","-","-","-","" "","3","-","-","-","-","-","50","3,206.55","3,323.65","550","14,900.00","100","0.55","0.70","200","0.40","0.85","94.46","113","19","151","" "","4","-","-","-","-","-","50","3,114.90","3,312.65","150","14,950.00","150","0.20","1.65","1,500","-","-","-","-","-","8","" "","7,792","-63","68","147.37","3,174.10","31.75","900","3,159.55","3,179.25","100","15,000.00","20,700","0.45","0.50","11,200","-","0.45","86.32","1,105","-39","14,890","" "","-","-","-","-","-","-","600","2,998.35","3,209.70","800","15,050.00","900","0.05","1.85","1,500","-","-","-","-","-","1","" "","21","-","-","-","-","-","950","2,976.90","3,132.80","1,200","15,100.00","500","0.40","0.75","200","-0.35","0.40","82.68","5","-1","38","" "","-","-","-","-","-","-","700","2,863.90","3,107.90","800","15,150.00","1,500","0.05","2.15","1,500","-","-","-","-","-","-","" "","104","-","-","-","-","-","500","2,870.05","3,052.30","500","15,200.00","1,500","0.35","0.65","250","0.10","0.65","83.51","5","-1","131","" "","-","-","-","-","-","-","600","2,767.75","3,005.40","550","15,250.00","1,500","0.05","1.80","1,500","-","-","-","-","-","-","" "","225","-","-","-","-","-","500","2,809.60","2,903.90","650","15,300.00","50","0.50","0.55","550","-0.10","0.50","79.42","4,551","7","706","" "","-","-","-","-","-","-","550","2,729.90","2,865.75","50","15,350.00","300","0.45","1.85","1,500","-","0.90","81.77","1","-","15","" "","128","-","-","-","-","-","1,050","2,679.00","2,840.25","1,150","15,400.00","200","0.50","0.55","1,750","0.10","0.55","75.25","138","1","545","" "","-","-","-","-","-","-","550","2,622.45","2,794.90","1,650","15,450.00","50","0.55","2.15","50","-","0.70","73.88","8","-5","17","" "","1,822","-","109","129.19","2,683.15","36.35","50","2,679.95","2,684.55","250","15,500.00","1,800","0.65","0.70","1,000","-0.10","0.70","76.04","1,958","-18","15,160","" "","-","-","-","-","-","-","850","2,530.45","2,693.50","900","15,550.00","200","0.55","0.90","50","-","-","-","-","-","29","" "","31","-","-","-","-","-","500","2,483.50","2,640.95","500","15,600.00","350","0.80","0.95","450","0.15","0.80","72.20","112","2","861","" "","-","-","-","-","-","-","550","2,388.65","2,598.65","550","15,650.00","600","0.30","1.75","300","-","-","-","-","-","3","" "","113","-10","17","141.75","2,500.00","60.00","50","2,464.70","2,491.60","50","15,700.00","100","0.70","0.85","50","-0.05","0.80","68.87","107","20","1,335","" "","-","-","-","-","-","-","1,800","2,390.40","2,439.10","1,800","15,750.00","600","0.40","0.75","1,000","-","-","-","-","-","61","" "","339","-","1","155.00","2,440.00","88.75","300","2,367.80","2,387.50","750","15,800.00","450","0.85","0.95","1,000","-0.05","0.90","69.17","149","-9","2,512","" "","1","-","-","-","-","-","1,800","2,299.75","2,339.35","1,800","15,850.00","600","0.40","1.60","200","-","-","-","-","-","4","" "","134","-","1","148.49","2,336.85","109.25","50","2,244.15","2,288.15","1,800","15,900.00","350","0.85","0.95","650","-0.05","0.95","65.93","137","20","673","" "","2","-","-","-","-","-","1,800","2,201.70","2,247.00","1,800","15,950.00","50","0.65","0.85","50","-","-","-","-","-","42","" "","4,987","-4","215","109.94","2,179.50","23.85","400","2,174.55","2,184.40","1,200","16,000.00","7,750","0.75","0.80","5,150","-0.10","0.80","62.32","3,158","277","28,167","" "","7","-","-","-","-","-","50","2,105.30","2,145.70","50","16,050.00","600","0.65","0.80","450","-0.35","0.80","61.65","24","-1","73","" "","222","-4","30","95.92","2,080.00","34.15","200","2,067.40","2,086.25","50","16,100.00","500","0.85","0.90","250","-0.20","0.85","61.22","1,332","90","1,083","" "","15","-","-","-","-","-","50","2,003.60","2,039.45","50","16,150.00","100","1.00","1.20","50","0.05","1.25","60.92","330","6","36","" "","378","-","30","117.78","2,000.50","54.05","100","1,964.40","1,983.30","200","16,200.00","1,200","0.95","1.05","900","-0.15","0.95","58.92","998","93","1,892","" "","2","-","-","-","-","-","50","1,900.75","1,937.35","50","16,250.00","300","0.75","1.15","200","0.05","1.10","57.19","32","1","186","" "","421","-1","60","123.00","1,924.75","72.80","50","1,867.85","1,883.35","400","16,300.00","500","0.95","1.05","800","-","1.00","55.75","1,196","72","6,340","" "","7","-","-","-","-","-","50","1,801.45","1,835.30","50","16,350.00","400","0.75","1.45","100","-","1.05","54.04","3","-2","121","" "","379","-3","4","107.85","1,800.00","52.25","100","1,756.40","1,786.80","50","16,400.00","1,350","1.05","1.10","400","0.15","1.15","52.61","1,066","73","2,135","" "","6","-","-","-","-","-","1,800","1,710.40","1,743.00","50","16,450.00","600","0.85","1.35","50","0.25","1.05","51.17","35","-","166","" "","8,410","-12","131","79.64","1,675.00","28.90","50","1,674.65","1,682.40","50","16,500.00","9,650","1.15","1.20","30,850","-0.05","1.15","50.95","16,562","-366","42,448","" "","19","-","5","76.01","1,603.10","-6.90","50","1,603.30","1,634.50","1,800","16,550.00","1,150","1.00","1.45","100","-0.25","0.85","47.22","4","2","298","" "","607","-","2","124.29","1,568.25","10.95","50","1,568.35","1,584.75","50","16,600.00","1,500","1.20","1.25","650","0.10","1.25","48.24","3,711","386","5,266","" "","454","-","-","-","-","-","50","1,505.05","1,535.45","50","16,650.00","200","1.15","1.55","50","0.55","1.65","47.91","77","-14","1,158","" "","1,358","-","32","72.45","1,457.00","0.10","50","1,472.75","1,485.40","450","16,700.00","4,550","1.30","1.35","5,000","0.05","1.30","45.51","4,816","420","8,757","" "","78","-","-","-","-","-","1,800","1,413.90","1,442.25","50","16,750.00","100","1.10","1.30","50","0.35","1.40","44.05","116","-8","316","" "","697","-","-","-","-","-","50","1,366.40","1,384.65","50","16,800.00","5,150","1.30","1.35","1,450","0.10","1.35","42.58","9,475","378","20,209","" "","53","-","5","96.21","1,379.65","138.65","50","1,291.80","1,347.15","50","16,850.00","800","1.45","1.55","1,200","0.60","1.95","41.78","720","312","697","" "","1,116","-1","14","93.02","1,328.00","70.00","50","1,271.70","1,283.95","50","16,900.00","8,600","1.40","1.45","3,750","-","1.45","39.97","11,865","-77","16,451","" "","44","1","2","82.17",

Extract_261021_112409

Please help me with the excel macro code.

Lekha mohanty · Oct 27, 2021

Thanks its working but its working for the first line only, at present my data is like this.

d.xlsm

A

1

"CALLS","","PUTS" " ","OI ","Chng in OI ","Volume ","IV ","LTP ","Chng ","Bid Qty ","Bid Price ","Ask Price ","Ask Qty ","Strike Price ","Bid Qty ","Bid Price ","Ask Price ","Ask Qty ","Chng ","LTP ","IV ","Volume ","Chng in OI ","OI "," " "","2,878","-51","58","202.89","3,935.80","40.65","50","3,910.95","3,927.95","50","14,250.00","5,050","0.40","0.45","7,400","0.10","0.40","106.60","2,665","-28","8,595","" "","173","-2","2","239.75","3,950.00","90.00","400","3,798.35","3,888.50","50","14,300.00","1,100","0.40","0.45","1,000","-","0.40","105.16","242","-62","855","" "","-","-","-","-","-","-","850","3,706.50","3,917.65","750","14,350.00","50","0.60","0.65","1,800","-0.05","0.70","108.93","39","17","73","" "","2","-","-","-","-","-","350","3,662.75","3,864.95","1,800","14,400.00","250","0.45","0.55","1,500","-0.05","0.45","103.33","59","-","209","" "","-","-","-","-","-","-","1,000","3,599.25","3,820.05","550","14,450.00","1,500","0.05","1.70","1,500","-","-","-","-","-","-","" "","4,425","-98","136","155.59","3,673.20","22.40","100","3,668.50","3,681.40","150","14,500.00","9,000","0.45","0.50","2,400","0.10","0.45","101.38","984","-200","10,897","" "","-","-","-","-","-","-","400","3,504.70","3,720.95","550","14,550.00","1,500","0.05","2.05","1,500","-","-","-","-","-","-","" "","47","-","-","-","-","-","700","3,451.70","3,665.80","500","14,600.00","450","0.55","0.60","1,500","-0.10","0.60","101.52","72","38","301","" "","-","-","-","-","-","-","600","3,406.30","3,621.30","1,800","14,650.00","1,500","0.15","2.10","1,500","-","-","-","-","-","-","" "","9","-","-","-","-","-","550","3,296.85","3,565.70","500","14,700.00","1,500","0.35","0.50","1,650","-","0.45","94.77","1","-","331","" "","-","-","-","-","-","-","700","3,309.30","3,515.70","750","14,750.00","1,500","0.05","2.10","1,800","-","-","-","-","-","-","" "","138","-1","1","236.32","3,504.25","164.25","1,000","3,282.55","3,457.00","50","14,800.00","150","0.55","0.75","200","-0.10","0.55","93.58","33","-21","18","" "","-","-","-","-","-","-","550","3,185.10","3,505.70","1,650","14,850.00","-","-","2.25","1,800","-","-","-","-","-","-","" "","3","-","-","-","-","-","50","3,206.55","3,323.65","550","14,900.00","100","0.55","0.70","200","0.40","0.85","94.46","113","19","151","" "","4","-","-","-","-","-","50","3,114.90","3,312.65","150","14,950.00","150","0.20","1.65","1,500","-","-","-","-","-","8","" "","7,792","-63","68","147.37","3,174.10","31.75","900","3,159.55","3,179.25","100","15,000.00","20,700","0.45","0.50","11,200","-","0.45","86.32","1,105","-39","14,890","" "","-","-","-","-","-","-","600","2,998.35","3,209.70","800","15,050.00","900","0.05","1.85","1,500","-","-","-","-","-","1","" "","21","-","-","-","-","-","950","2,976.90","3,132.80","1,200","15,100.00","500","0.40","0.75","200","-0.35","0.40","82.68","5","-1","38","" "","-","-","-","-","-","-","700","2,863.90","3,107.90","800","15,150.00","1,500","0.05","2.15","1,500","-","-","-","-","-","-","" "","104","-","-","-","-","-","500","2,870.05","3,052.30","500","15,200.00","1,500","0.35","0.65","250","0.10","0.65","83.51","5","-1","131","" "","-","-","-","-","-","-","600","2,767.75","3,005.40","550","15,250.00","1,500","0.05","1.80","1,500","-","-","-","-","-","-","" "","225","-","-","-","-","-","500","2,809.60","2,903.90","650","15,300.00","50","0.50","0.55","550","-0.10","0.50","79.42","4,551","7","706","" "","-","-","-","-","-","-","550","2,729.90","2,865.75","50","15,350.00","300","0.45","1.85","1,500","-","0.90","81.77","1","-","15","" "","128","-","-","-","-","-","1,050","2,679.00","2,840.25","1,150","15,400.00","200","0.50","0.55","1,750","0.10","0.55","75.25","138","1","545","" "","-","-","-","-","-","-","550","2,622.45","2,794.90","1,650","15,450.00","50","0.55","2.15","50","-","0.70","73.88","8","-5","17","" "","1,822","-","109","129.19","2,683.15","36.35","50","2,679.95","2,684.55","250","15,500.00","1,800","0.65","0.70","1,000","-0.10","0.70","76.04","1,958","-18","15,160","" "","-","-","-","-","-","-","850","2,530.45","2,693.50","900","15,550.00","200","0.55","0.90","50","-","-","-","-","-","29","" "","31","-","-","-","-","-","500","2,483.50","2,640.95","500","15,600.00","350","0.80","0.95","450","0.15","0.80","72.20","112","2","861","" "","-","-","-","-","-","-","550","2,388.65","2,598.65","550","15,650.00","600","0.30","1.75","300","-","-","-","-","-","3","" "","113","-10","17","141.75","2,500.00","60.00","50","2,464.70","2,491.60","50","15,700.00","100","0.70","0.85","50","-0.05","0.80","68.87","107","20","1,335","" "","-","-","-","-","-","-","1,800","2,390.40","2,439.10","1,800","15,750.00","600","0.40","0.75","1,000","-","-","-","-","-","61","" "","339","-","1","155.00","2,440.00","88.75","300","2,367.80","2,387.50","750","15,800.00","450","0.85","0.95","1,000","-0.05","0.90","69.17","149","-9","2,512","" "","1","-","-","-","-","-","1,800","2,299.75","2,339.35","1,800","15,850.00","600","0.40","1.60","200","-","-","-","-","-","4","" "","134","-","1","148.49","2,336.85","109.25","50","2,244.15","2,288.15","1,800","15,900.00","350","0.85","0.95","650","-0.05","0.95","65.93","137","20","673","" "","2","-","-","-","-","-","1,800","2,201.70","2,247.00","1,800","15,950.00","50","0.65","0.85","50","-","-","-","-","-","42","" "","4,987","-4","215","109.94","2,179.50","23.85","400","2,174.55","2,184.40","1,200","16,000.00","7,750","0.75","0.80","5,150","-0.10","0.80","62.32","3,158","277","28,167","" "","7","-","-","-","-","-","50","2,105.30","2,145.70","50","16,050.00","600","0.65","0.80","450","-0.35","0.80","61.65","24","-1","73","" "","222","-4","30","95.92","2,080.00","34.15","200","2,067.40","2,086.25","50","16,100.00","500","0.85","0.90","250","-0.20","0.85","61.22","1,332","90","1,083","" "","15","-","-","-","-","-","50","2,003.60","2,039.45","50","16,150.00","100","1.00","1.20","50","0.05","1.25","60.92","330","6","36","" "","378","-","30","117.78","2,000.50","54.05","100","1,964.40","1,983.30","200","16,200.00","1,200","0.95","1.05","900","-0.15","0.95","58.92","998","93","1,892","" "","2","-","-","-","-","-","50","1,900.75","1,937.35","50","16,250.00","300","0.75","1.15","200","0.05","1.10","57.19","32","1","186","" "","421","-1","60","123.00","1,924.75","72.80","50","1,867.85","1,883.35","400","16,300.00","500","0.95","1.05","800","-","1.00","55.75","1,196","72","6,340","" "","7","-","-","-","-","-","50","1,801.45","1,835.30","50","16,350.00","400","0.75","1.45","100","-","1.05","54.04","3","-2","121","" "","379","-3","4","107.85","1,800.00","52.25","100","1,756.40","1,786.80","50","16,400.00","1,350","1.05","1.10","400","0.15","1.15","52.61","1,066","73","2,135","" "","6","-","-","-","-","-","1,800","1,710.40","1,743.00","50","16,450.00","600","0.85","1.35","50","0.25","1.05","51.17","35","-","166","" "","8,410","-12","131","79.64","1,675.00","28.90","50","1,674.65","1,682.40","50","16,500.00","9,650","1.15","1.20","30,850","-0.05","1.15","50.95","16,562","-366","42,448","" "","19","-","5","76.01","1,603.10","-6.90","50","1,603.30","1,634.50","1,800","16,550.00","1,150","1.00","1.45","100","-0.25","0.85","47.22","4","2","298","" "","607","-","2","124.29","1,568.25","10.95","50","1,568.35","1,584.75","50","16,600.00","1,500","1.20","1.25","650","0.10","1.25","48.24","3,711","386","5,266","" "","454","-","-","-","-","-","50","1,505.05","1,535.45","50","16,650.00","200","1.15","1.55","50","0.55","1.65","47.91","77","-14","1,158","" "","1,358","-","32","72.45","1,457.00","0.10","50","1,472.75","1,485.40","450","16,700.00","4,550","1.30","1.35","5,000","0.05","1.30","45.51","4,816","420","8,757","" "","78","-","-","-","-","-","1,800","1,413.90","1,442.25","50","16,750.00","100","1.10","1.30","50","0.35","1.40","44.05","116","-8","316","" "","697","-","-","-","-","-","50","1,366.40","1,384.65","50","16,800.00","5,150","1.30","1.35","1,450","0.10","1.35","42.58","9,475","378","20,209","" "","53","-","5","96.21","1,379.65","138.65","50","1,291.80","1,347.15","50","16,850.00","800","1.45","1.55","1,200","0.60","1.95","41.78","720","312","697","" "","1,116","-1","14","93.02","1,328.00","70.00","50","1,271.70","1,283.95","50","16,900.00","8,600","1.40","1.45","3,750","-","1.45","39.97","11,865","-77","16,451","" "","44","1","2","82.17",

2

Extract_261021_112409

Cell Formulas
Range	Formula
A2	A2	=RemoveCommas(A1)

Peter_SSs · Oct 28, 2021

It is a bit hard to keep up with exactly what you have, where and what you want as the information keeps changing.
However, based on post #11 sample data ..

Lekha mohanty said:
at present my data is like this.

.. see if this does what you want.

VBA Code:

Sub Split_Data()
  Dim V As Variant
 
  V = Split(Replace(Replace(Replace(Mid(Range("A1").Text, 20), """,""", ";"), ",", ""), """", ""), vbLf)
  Application.ScreenUpdating = False
  With Range("B1").Resize(UBound(V) + 1)
    .Value = Application.Transpose(V)
    .TextToColumns DataType:=xlDelimited, Semicolon:=True, Other:=False
    .Offset(, 1).CurrentRegion.Columns.AutoFit
    .EntireColumn.Delete
  End With
  Application.ScreenUpdating = True
End Sub

For sample data as shown in cell A1 of post #11, this is my result from the above code.

Lekha mohanty.xlsm

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

OI

Chng in OI

Volume

IV

LTP

Chng

Bid Qty

Bid Price

Ask Price

Ask Qty

Strike Price

Bid Qty

Bid Price

Ask Price

Ask Qty

Chng

LTP

IV

Volume

Chng in OI

OI

2

2878

-51

58

202.89

3935.8

40.65

50

3910.95

3927.95

50

14250

5050

0.4

0.45

7400

0.1

0.4

106.6

2665

-28

8595

3

173

-2

2

239.75

3950

90

400

3798.35

3888.5

50

14300

1100

0.4

0.45

1000

-

0.4

105.16

242

-62

855

4

-

850

3706.5

3917.65

750

14350

50

0.6

0.65

1800

-0.05

0.7

108.93

39

17

73

5

2

-

350

3662.75

3864.95

1800

14400

250

0.45

0.55

1500

-0.05

0.45

103.33

59

-

209

6

-

1000

3599.25

3820.05

550

14450

1500

0.05

1.7

1500

-

7

4425

-98

136

155.59

3673.2

22.4

100

3668.5

3681.4

150

14500

9000

0.45

0.5

2400

0.1

0.45

101.38

984

-200

10897

8

-

400

3504.7

3720.95

550

14550

1500

0.05

2.05

1500

-

9

47

-

700

3451.7

3665.8

500

14600

450

0.55

0.6

1500

-0.1

0.6

101.52

72

38

301

10

-

600

3406.3

3621.3

1800

14650

1500

0.15

2.1

1500

-

11

9

-

550

3296.85

3565.7

500

14700

1500

0.35

0.5

1650

-

0.45

94.77

1

-

331

12

-

700

3309.3

3515.7

750

14750

1500

0.05

2.1

1800

-

13

138

-1

1

236.32

3504.25

164.25

1000

3282.55

3457

50

14800

150

0.55

0.75

200

-0.1

0.55

93.58

33

-21

18

14

-

550

3185.1

3505.7

1650

14850

-

2.25

1800

-

15

3

-

50

3206.55

3323.65

550

14900

100

0.55

0.7

200

0.4

0.85

94.46

113

19

151

16

4

-

50

3114.9

3312.65

150

14950

150

0.2

1.65

1500

-

8

17

7792

-63

68

147.37

3174.1

31.75

900

3159.55

3179.25

100

15000

20700

0.45

0.5

11200

-

0.45

86.32

1105

-39

14890

18

-

600

2998.35

3209.7

800

15050

900

0.05

1.85

1500

-

1

19

21

-

950

2976.9

3132.8

1200

15100

500

0.4

0.75

200

-0.35

0.4

82.68

5

-1

38

20

-

700

2863.9

3107.9

800

15150

1500

0.05

2.15

1500

-

21

104

-

500

2870.05

3052.3

500

15200

1500

0.35

0.65

250

0.1

0.65

83.51

5

-1

131

22

-

600

2767.75

3005.4

550

15250

1500

0.05

1.8

1500

-

23

225

-

500

2809.6

2903.9

650

15300

50

0.5

0.55

550

-0.1

0.5

79.42

4551

7

706

24

-

550

2729.9

2865.75

50

15350

300

0.45

1.85

1500

-

0.9

81.77

1

-

15

25

128

-

1050

2679

2840.25

1150

15400

200

0.5

0.55

1750

0.1

0.55

75.25

138

1

545

26

-

550

2622.45

2794.9

1650

15450

50

0.55

2.15

50

-

0.7

73.88

8

-5

17

27

1822

-

109

129.19

2683.15

36.35

50

2679.95

2684.55

250

15500

1800

0.65

0.7

1000

-0.1

0.7

76.04

1958

-18

15160

28

-

850

2530.45

2693.5

900

15550

200

0.55

0.9

50

-

29

31

-

500

2483.5

2640.95

500

15600

350

0.8

0.95

450

0.15

0.8

72.2

112

2

861

30

-

550

2388.65

2598.65

550

15650

600

0.3

1.75

300

-

3

31

113

-10

17

141.75

2500

60

50

2464.7

2491.6

50

15700

100

0.7

0.85

50

-0.05

0.8

68.87

107

20

1335

32

-

1800

2390.4

2439.1

1800

15750

600

0.4

0.75

1000

-

61

33

339

-

1

155

2440

88.75

300

2367.8

2387.5

750

15800

450

0.85

0.95

1000

-0.05

0.9

69.17

149

-9

2512

34

1

-

1800

2299.75

2339.35

1800

15850

600

0.4

1.6

200

-

4

35

134

-

1

148.49

2336.85

109.25

50

2244.15

2288.15

1800

15900

350

0.85

0.95

650

-0.05

0.95

65.93

137

20

673

36

2

-

1800

2201.7

2247

1800

15950

50

0.65

0.85

50

-

42

37

4987

-4

215

109.94

2179.5

23.85

400

2174.55

2184.4

1200

16000

7750

0.75

0.8

5150

-0.1

0.8

62.32

3158

277

28167

38

7

-

50

2105.3

2145.7

50

16050

600

0.65

0.8

450

-0.35

0.8

61.65

24

-1

73

39

222

-4

30

95.92

2080

34.15

200

2067.4

2086.25

50

16100

500

0.85

0.9

250

-0.2

0.85

61.22

1332

90

1083

40

15

-

50

2003.6

2039.45

50

16150

100

1

1.2

50

0.05

1.25

60.92

330

6

36

41

378

-

30

117.78

2000.5

54.05

100

1964.4

1983.3

200

16200

1200

0.95

1.05

900

-0.15

0.95

58.92

998

93

1892

42

2

-

50

1900.75

1937.35

50

16250

300

0.75

1.15

200

0.05

1.1

57.19

32

1

186

43

421

-1

60

123

1924.75

72.8

50

1867.85

1883.35

400

16300

500

0.95

1.05

800

-

1

55.75

1196

72

6340

44

7

-

50

1801.45

1835.3

50

16350

400

0.75

1.45

100

-

1.05

54.04

3

-2

121

45

379

-3

4

107.85

1800

52.25

100

1756.4

1786.8

50

16400

1350

1.05

1.1

400

0.15

1.15

52.61

1066

73

2135

46

6

-

1800

1710.4

1743

50

16450

600

0.85

1.35

50

0.25

1.05

51.17

35

-

166

47

8410

-12

131

79.64

1675

28.9

50

1674.65

1682.4

50

16500

9650

1.15

1.2

30850

-0.05

1.15

50.95

16562

-366

42448

48

19

-

5

76.01

1603.1

-6.9

50

1603.3

1634.5

1800

16550

1150

1

1.45

100

-0.25

0.85

47.22

4

2

298

49

607

-

2

124.29

1568.25

10.95

50

1568.35

1584.75

50

16600

1500

1.2

1.25

650

0.1

1.25

48.24

3711

386

5266

50

454

-

50

1505.05

1535.45

50

16650

200

1.15

1.55

50

0.55

1.65

47.91

77

-14

1158

51

1358

-

32

72.45

1457

0.1

50

1472.75

1485.4

450

16700

4550

1.3

1.35

5000

0.05

1.3

45.51

4816

420

8757

52

78

-

1800

1413.9

1442.25

50

16750

100

1.1

1.3

50

0.35

1.4

44.05

116

-8

316

53

697

-

50

1366.4

1384.65

50

16800

5150

1.3

1.35

1450

0.1

1.35

42.58

9475

378

20209

54

53

-

5

96.21

1379.65

138.65

50

1291.8

1347.15

50

16850

800

1.45

1.55

1200

0.6

1.95

41.78

720

312

697

55

1116

-1

14

93.02

1328

70

50

1271.7

1283.95

50

16900

8600

1.4

1.45

3750

-

1.45

39.97

11865

-77

16451

56

44

1

2

82.17

Test

Lekha mohanty · Oct 29, 2021

Peter_SSs said:
It is a bit hard to keep up with exactly what you have, where and what you want as the information keeps changing. However, based on post #11 sample data .. .. see if this does what you want.

VBA Code:

Sub Split_Data() Dim V As Variant V = Split(Replace(Replace(Replace(Mid(Range("A1").Text, 20), """,""", ";"), ",", ""), """", ""), vbLf) Application.ScreenUpdating = False With Range("B1").Resize(UBound(V) + 1) .Value = Application.Transpose(V) .TextToColumns DataType:=xlDelimited, Semicolon:=True, Other:=False .Offset(, 1).CurrentRegion.Columns.AutoFit .EntireColumn.Delete End With Application.ScreenUpdating = True End Sub

For sample data as shown in cell A1 of post #11, this is my result from the above code.
Lekha mohanty.xlsm
B C D E F G H I J K L M N O P Q R S T U V
1 OI Chng in OI Volume IV LTP Chng Bid Qty Bid Price Ask Price Ask Qty Strike Price Bid Qty Bid Price Ask Price Ask Qty Chng LTP IV Volume Chng in OI OI
2 2878 -51 58 202.89 3935.8 40.65 50 3910.95 3927.95 50 14250 5050 0.4 0.45 7400 0.1 0.4 106.6 2665 -28 8595
3 173 -2 2 239.75 3950 90 400 3798.35 3888.5 50 14300 1100 0.4 0.45 1000 - 0.4 105.16 242 -62 855
4 - - - - - - 850 3706.5 3917.65 750 14350 50 0.6 0.65 1800 -0.05 0.7 108.93 39 17 73
5 2 - - - - - 350 3662.75 3864.95 1800 14400 250 0.45 0.55 1500 -0.05 0.45 103.33 59 - 209
6 - - - - - - 1000 3599.25 3820.05 550 14450 1500 0.05 1.7 1500 - - - - - -
7 4425 -98 136 155.59 3673.2 22.4 100 3668.5 3681.4 150 14500 9000 0.45 0.5 2400 0.1 0.45 101.38 984 -200 10897
8 - - - - - - 400 3504.7 3720.95 550 14550 1500 0.05 2.05 1500 - - - - - -
9 47 - - - - - 700 3451.7 3665.8 500 14600 450 0.55 0.6 1500 -0.1 0.6 101.52 72 38 301
10 - - - - - - 600 3406.3 3621.3 1800 14650 1500 0.15 2.1 1500 - - - - - -
11 9 - - - - - 550 3296.85 3565.7 500 14700 1500 0.35 0.5 1650 - 0.45 94.77 1 - 331
12 - - - - - - 700 3309.3 3515.7 750 14750 1500 0.05 2.1 1800 - - - - - -
13 138 -1 1 236.32 3504.25 164.25 1000 3282.55 3457 50 14800 150 0.55 0.75 200 -0.1 0.55 93.58 33 -21 18
14 - - - - - - 550 3185.1 3505.7 1650 14850 - - 2.25 1800 - - - - - -
15 3 - - - - - 50 3206.55 3323.65 550 14900 100 0.55 0.7 200 0.4 0.85 94.46 113 19 151
16 4 - - - - - 50 3114.9 3312.65 150 14950 150 0.2 1.65 1500 - - - - - 8
17 7792 -63 68 147.37 3174.1 31.75 900 3159.55 3179.25 100 15000 20700 0.45 0.5 11200 - 0.45 86.32 1105 -39 14890
18 - - - - - - 600 2998.35 3209.7 800 15050 900 0.05 1.85 1500 - - - - - 1
19 21 - - - - - 950 2976.9 3132.8 1200 15100 500 0.4 0.75 200 -0.35 0.4 82.68 5 -1 38
20 - - - - - - 700 2863.9 3107.9 800 15150 1500 0.05 2.15 1500 - - - - - -
21 104 - - - - - 500 2870.05 3052.3 500 15200 1500 0.35 0.65 250 0.1 0.65 83.51 5 -1 131
22 - - - - - - 600 2767.75 3005.4 550 15250 1500 0.05 1.8 1500 - - - - - -
23 225 - - - - - 500 2809.6 2903.9 650 15300 50 0.5 0.55 550 -0.1 0.5 79.42 4551 7 706
24 - - - - - - 550 2729.9 2865.75 50 15350 300 0.45 1.85 1500 - 0.9 81.77 1 - 15
25 128 - - - - - 1050 2679 2840.25 1150 15400 200 0.5 0.55 1750 0.1 0.55 75.25 138 1 545
26 - - - - - - 550 2622.45 2794.9 1650 15450 50 0.55 2.15 50 - 0.7 73.88 8 -5 17
27 1822 - 109 129.19 2683.15 36.35 50 2679.95 2684.55 250 15500 1800 0.65 0.7 1000 -0.1 0.7 76.04 1958 -18 15160
28 - - - - - - 850 2530.45 2693.5 900 15550 200 0.55 0.9 50 - - - - - 29
29 31 - - - - - 500 2483.5 2640.95 500 15600 350 0.8 0.95 450 0.15 0.8 72.2 112 2 861
30 - - - - - - 550 2388.65 2598.65 550 15650 600 0.3 1.75 300 - - - - - 3
31 113 -10 17 141.75 2500 60 50 2464.7 2491.6 50 15700 100 0.7 0.85 50 -0.05 0.8 68.87 107 20 1335
32 - - - - - - 1800 2390.4 2439.1 1800 15750 600 0.4 0.75 1000 - - - - - 61
33 339 - 1 155 2440 88.75 300 2367.8 2387.5 750 15800 450 0.85 0.95 1000 -0.05 0.9 69.17 149 -9 2512
34 1 - - - - - 1800 2299.75 2339.35 1800 15850 600 0.4 1.6 200 - - - - - 4
35 134 - 1 148.49 2336.85 109.25 50 2244.15 2288.15 1800 15900 350 0.85 0.95 650 -0.05 0.95 65.93 137 20 673
36 2 - - - - - 1800 2201.7 2247 1800 15950 50 0.65 0.85 50 - - - - - 42
37 4987 -4 215 109.94 2179.5 23.85 400 2174.55 2184.4 1200 16000 7750 0.75 0.8 5150 -0.1 0.8 62.32 3158 277 28167
38 7 - - - - - 50 2105.3 2145.7 50 16050 600 0.65 0.8 450 -0.35 0.8 61.65 24 -1 73
39 222 -4 30 95.92 2080 34.15 200 2067.4 2086.25 50 16100 500 0.85 0.9 250 -0.2 0.85 61.22 1332 90 1083
40 15 - - - - - 50 2003.6 2039.45 50 16150 100 1 1.2 50 0.05 1.25 60.92 330 6 36
41 378 - 30 117.78 2000.5 54.05 100 1964.4 1983.3 200 16200 1200 0.95 1.05 900 -0.15 0.95 58.92 998 93 1892
42 2 - - - - - 50 1900.75 1937.35 50 16250 300 0.75 1.15 200 0.05 1.1 57.19 32 1 186
43 421 -1 60 123 1924.75 72.8 50 1867.85 1883.35 400 16300 500 0.95 1.05 800 - 1 55.75 1196 72 6340
44 7 - - - - - 50 1801.45 1835.3 50 16350 400 0.75 1.45 100 - 1.05 54.04 3 -2 121
45 379 -3 4 107.85 1800 52.25 100 1756.4 1786.8 50 16400 1350 1.05 1.1 400 0.15 1.15 52.61 1066 73 2135
46 6 - - - - - 1800 1710.4 1743 50 16450 600 0.85 1.35 50 0.25 1.05 51.17 35 - 166
47 8410 -12 131 79.64 1675 28.9 50 1674.65 1682.4 50 16500 9650 1.15 1.2 30850 -0.05 1.15 50.95 16562 -366 42448
48 19 - 5 76.01 1603.1 -6.9 50 1603.3 1634.5 1800 16550 1150 1 1.45 100 -0.25 0.85 47.22 4 2 298
49 607 - 2 124.29 1568.25 10.95 50 1568.35 1584.75 50 16600 1500 1.2 1.25 650 0.1 1.25 48.24 3711 386 5266
50 454 - - - - - 50 1505.05 1535.45 50 16650 200 1.15 1.55 50 0.55 1.65 47.91 77 -14 1158
51 1358 - 32 72.45 1457 0.1 50 1472.75 1485.4 450 16700 4550 1.3 1.35 5000 0.05 1.3 45.51 4816 420 8757
52 78 - - - - - 1800 1413.9 1442.25 50 16750 100 1.1 1.3 50 0.35 1.4 44.05 116 -8 316
53 697 - - - - - 50 1366.4 1384.65 50 16800 5150 1.3 1.35 1450 0.1 1.35 42.58 9475 378 20209
54 53 - 5 96.21 1379.65 138.65 50 1291.8 1347.15 50 16850 800 1.45 1.55 1200 0.6 1.95 41.78 720 312 697
55 1116 -1 14 93.02 1328 70 50 1271.7 1283.95 50 16900 8600 1.4 1.45 3750 - 1.45 39.97 11865 -77 16451
56 44 1 2 82.17
Test

Thank you so much

Peter_SSs · Oct 29, 2021

Lekha mohanty said:
Thank you so much

You're welcome.

Removing Commas in between quotation mark and numbers Excel Macro

Lekha mohanty

New Member

Lekha mohanty

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Lekha mohanty

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Removing Commas in between quotation mark and numbers Excel Macro

Lekha mohanty

New Member

Lekha mohanty

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Lekha mohanty

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock