How to strip of the characters in a cell and copy the filtered data into another workbook

[sairampappu]

Hi,

I am new to VBA and trying to build a macro. I need to select a particular entire column which has some units for example say distance 72.4 Km/h (this value may varied and is applicable for single cell). I need to strip off the units and get only 72.4 and that should be copied to another workbook.

1. If there is a column R1C1 has a heading "distance", in R2C1 has some value 72.34 Km/h. Now I need to get take out the units and get 72.34, paste the same value in another work book.and this should happen for entire column.

Can Someone help me out.

Thanks,
Sai

 

[alansidman]

try this: =LEFT(A1,FIND(" ",A1))

 

[Fluff]

Alternatively, select the column, hit Ctrl H & enter " Km/h" in the Find What box & leave the Replace with box empty & then click Replace All.
If you need a macro you can use the macro recorder whilst you do the above

 

[Rick Rothstein]

Alternatively, select the column, hit Ctrl H & enter " Km/h" in the Find What box & leave the Replace with box empty & then click Replace All.

Or alternately, if the units are are not always "Km/h", then use " *" (space asterisk) in the "Find what" field instead.

Also, I would add one more thing thing to Fluff's instruction... before clicking "Replace All", you should click the "Options>>" button and make sure the two checkboxes do not have check marks in them, then click the "Replace All" button.

 

[sairampappu]

Rick,

That is correct. Km/h is only in one column. There are many other units for time = seconds, angle= degrees/radians in different cells. I want all of the other units should be stripped off.



sai