Isolate and remove alpha characters and leave numeric.
Posted by Wingz on January 31, 2002 11:09 AM
Can anyone tell me the best way to write a macro that will take an alpha/numeric filled cell and remove all the alpha characters? IE. I have a huge sheet of cells that read : 2.3kg and I want them just to read 2.3 (or whatever number is in the cell)
Any help would be great because I really don't look forward to going through each cell and removing the text! :)
Posted by Barrie Davidson on January 31, 2002 11:14 AM
If you highlight the range you want to remove the kg from, you can perform a search and replace. In the "find what:" box, type "kg" (without the quotes) and leave the "replace with:" box blank. Then click on "Replace all".
Does this help you out?
BarrieBarrie Davidson
Posted by Wingz on January 31, 2002 11:24 AM
Well it does and it doesn't. That's a nice little tool (thanks) for eliminating the alpha, however, i wish to use it in conjuntion with a macro formula that will in turn perform a calculation on the "new" contents of the cell, say the 2.3
The method you suggested leaves the 2.3 as a string as opposed to a number as well. Any more thoughts?
Posted by Barrie Davidson on January 31, 2002 11:47 AM
Well, if you want to convert the 2.3 to a value (from a string), select the range and (from the main menu) select Data|TextToColumns and click on the finish button.
What kind of calculation to the new contents?
BarrieBarrie Davidson
Posted by Wingz on January 31, 2002 11:52 AM
It's a simple KG to Pound conversion. So I want to : Remove the text from the cell
Take the value and * by 2.2
That's pretty much it!
Thanks for your help.
Posted by Barrie Davidson on January 31, 2002 1:03 PM
How about using an adjacent column and putting in a formula to convert. That way you keep kg and lb. Something like:
=A1*2.2
Column A has your kg's and the column with the above formula will have lb's.
Does this work?
BarrieBarrie Davidson
