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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Convert text to number for currency
- Thread starter Djbdrake
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,131
- Office Version
- 365
- Platform
- 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.
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.
Upvote
0
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 :
<colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 2986;">
<tbody>
</tbody>
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 |
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,131
- Office Version
- 365
- Platform
- 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.
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.
Upvote
0
