Text to Columns in Macro

[JimM]

I'm trying to create a macro so that it converts a column of text to a number by using Text to Columns. I've created the macro using the recorder which worked fine but when I run the macro itself it's not updating the cells ie they still appear to be text. However if I then go into a cell, the cell descriptor shows it as General and if I F2 to edit it changes the cell to a value.
I've tried creating it as delimited and fixed width but still no joy
Any ideas of why the macro is not working as it should?

Thanks


The relevant code I'm using is

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

 

[Alex Blakenburg]

If you are using VBA anyway try this:

Sub ConvertValue()
    With Sheets("DFC")
        With Intersect(.Columns("A:A"), .UsedRange)
            .Value = .Value
        End With
    End With
End Sub

 

[JimM]

Thanks for the suggestion but that's not worked

 

[Alex Blakenburg]

What is the column formatting at the starting point ? Is it General at the start ? If so tell me what number formatting you want applied.
Can you provide an XL2BB of some of the data ?

 

[JimM]

OK, not sure if this is a red herring but think there might be an invisible space at the end of each number. I've recorded a macro that mimics the F2 process and it's

Range("S4").Select
ActiveCell.FormulaR1C1 = "$55,983.60 "

See the empty space before the last "

I can't see the space when I go into the cell but is it likely that it's causing the issue

 

[JimM]

What is the column formatting at the starting point ? Is it General at the start ? If so tell me what number formatting you want applied.
Can you provide an XL2BB of some of the data ?

Looks like start formatting is General, I need it to be a number or currency, basically something that I can sum up

Sorry can't upload as it's a work PC

 

[Alex Blakenburg]

What is your default currency symbol ?

 

[JimM]

I'm in the UK so it should be £ and when I go into format cells, currency the symbol drop down box has a £ in it

 

[JimM]

Sorry also just realised that when the column is being populated the source data has the £ attached, so when it's pasted into my spreadsheet it's pasting the £ as part of the cell contents - this may be why it's treating it as text ?
When I F2 in the cell it removes the leading £ but still displays it due to the formatting (hope that makes sense)

 

[JimM]

I've fixed it! Instead of using Text to Columns I've simply used find and replace to remove the £ and my data is now working as I need it

Thanks for taking the time to respond though, it put me onto the solution!