Convert text to number for currency

[Djbdrake]

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?

 

[Joe4]

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]

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 :

[TABLE="width: 63"]
<colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 2986;"> <tbody>[TR]
[TD="width: 84, bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£20.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£20.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£2.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£2.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£2.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£2.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£2.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£2.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£2.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£20.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£20.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]£75.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

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.