Automation of Text into Number

[suryaprasad]

I have Sheet which contains Age in Text for Ex : 74 Years but i need to Convert in Number 74 so that i can calculate the total age for my summary updation. And Additional to this there are Many spaces , commas ,dot and some time the age can be displayed as 24 Months and which should be as 2 years and instead fo years it will be as year . so here please can any help with the attached excel sheet which convert all the ages and replace the updated age in the same column only.

For Ex:

Col A After applying the VB the Output needed is ColA
74 Years 74
20 Year 20
2 Days 1
24 Months 2

Please any help me out the change the text format of age in number format age.

Excel Sheet attached.

[caholmes]

Here is a function that may help you. Follow these instructions

• Go into the Visual Basic Editor (Alt + F11)
• Insert a module from the Insert Menu
• Paste in this code (see below)
• In cell B2 type =GetNumber(A2).
• Copy your new formula down to other cells using the normal methods (Autofill)
• Copy and paste the results as values over the existing data

Please Login or Register  to view this content.

[suryaprasad]

The Formula nor the code are working.

[ravishankar]

Hi
try the macro in the attached excel file
Ravi