Convert text to number for currency

Djbdrake

New Member
Joined
Oct 2, 2019
Messages
6
I need to write a macro for converting text to number. I have tried recording when I do text to columns but when I tried to run the macro under the same conditions as when it was recorded then it doesn't wok and the numbers stay as text. What do I need to do to make the text convert to a number?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Make sure you format the column first (to something other than "Text", like "General" or some number option), then do Text to Columns on that column. If you record that, it should so what you want, as long as you are always running it against the same column every time.

If that doesn't work, please post your recorded code along with a some sampling of your data entries.
 

Djbdrake

New Member
Joined
Oct 2, 2019
Messages
6
I tried doing that but it wouldn't work when I reset everything and tried running it again. The code came out as:

Range("D12:D50").Select
Selection.TextToColumns Destination:=Range("D12"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

And the data set is :

£10.00
£20.00
£20.00
£2.50
£2.50
£2.50
£2.50
£2.50
£10.00
£10.00
£10.00
£10.00
£10.00
£10.00
£10.00
£10.00
£2.00
£2.00
£10.00
£20.00
£20.00
£75.00
<colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 2986;"> <tbody> </tbody>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows
If it is text, and the "£" symbol is in there as literal text, then you will need to remove that first.
Try doing a "Search and Replace", replacing the "
£" with nothing.
That, in and of itself, may be enough to convert all the entries to numbers. If not, do the Text to Columns after that.
 

Forum statistics

Threads
1,143,677
Messages
5,720,249
Members
422,272
Latest member
ginkgoVil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top