Live Course on Drafting of Appeal and Reply of Penalty Notice by CA Sachin Sinha. Register Now!!

Text Numeric to Numeric

bhuvana (Accounts) (150 Points)

11 January 2010

Am copying bank statement from online banking. The numeric colum is in text format. how to change it to numeric format, so that addition of entries can be made there itself.....

my online search gave the following tip as solution...but it doesnt work :(

pls help me out (if this topic has already been discussed here in the forum, pls give me the link )

• Select an empty cell in your worksheet and type a 1.
• Copy the cell containing the 1
• Select the range of data that is stored as text
• Select Edit| Paste Special
• Select Multiply from the Operation section of the dialog box
• Click on OK

All of the data will now be numeric and you are able to analyze and perform mathematical calculations on it.

Reply
Follow
Share

4 Replies

Exshail (Software) (1576 Points)
Replied 11 January 2010

It should work. try again.

Or you can use below vba macro code to convert it to value

Sub convvalue()

For Each d In Selection

d.Value = d.Value

End Sub

Goto VBA Editor by pressing Alt-F11 or Tools>Macro>Visual Basic Editor

Insert Module

Paste above code & run with your text numric cells selected.

From VBA Editor press F5 or from Excel Tools>Macro>Macros>select convvalue & click on run button.

Shailesh

1 Like

Ram Avtar Singh (Nagari Sultanpur U.P.Delhi) (14477 Points)
Replied 11 January 2010

Shailesh ji You are most welcome in this forum. actuly i was waiting for genious persons like you . you can also help me for serch commond. Party wise, sundry debtors detail. in Excel 2007 in VBA Programe.

Party Name & Contct No.

Bill Date Amount

Total

Thanks

bhuvana (Accounts) (150 Points)
Replied 11 January 2010

Shailesh ji,

thanQ for your immediate response. Thanx for the code…but sorry to say that it still doesn’t work. Now I have attached the file with the text (numeric). The figures have an empty space after decimcals (this i found only after posting the query here). If I remove it manually in each cell, ur code and the online available tip, both works…but, its not possible to remove that xtra space na for that many entires....!!!!

Attached File : 46 ca club.xls downloaded: 180 times

Exshail (Software) (1576 Points)
Replied 11 January 2010

Originally posted by : bhuvana
	Shailesh ji, thanQ for your immediate response. Thanx for the code…but sorry to say that it still doesn’t work. Now I have attached the file with the text (numeric). The figures have an empty space after decimcals (this i found only after posting the query here). If I remove it manually in each cell, ur code and the online available tip, both works…but, its not possible to remove that xtra space na for that many entires....!!!!