Changing text string to number?

[dm59]

Hello
I get regular exports from SAP that has a field intended to show gross square footage of a buildings but it gives it to me in text format, as so:
50,000 ft2

I'd really like to have code that allows me to select that column and have it remove the " ft2" from the end and have just the "50,000" as a number with no decimal places and the comma separating thousands.

Any thoughts? Thanks in advance.

 

[Weaver]

what happens if you do a find/replace on the column and replace ft2 with a null (blank)?

 

[mooseman]

Replace the column letter with whatever you need. add this to your macro code.

Columns("A:A").Replace What:=" ft2", Replacement:=""

And for the formatting, i think the code you want is something like this:

Columns("A:A").Numberformat = "#,##0"

The "#,##0" part is a number format with no decimals and uses the "comma" separator.

 

[MickG]

Hi, This will replace in selection.

Sub Ft()
Dim Dn As Range
For Each Dn In Selection
    If Right(Dn, 3) = "ft2" Then
        Dn.Value = Replace(Left(Dn.Value, InStr(Dn, Chr(32))), ",", "")
    End If
Next Dn
End Sub

Mick

 

[dm59]

Thanks all for the assistance.

excelR8R
I tried that but I was hoping for a one shot deal.

MickG
I took your code and put in the "#,##0" part from Mooseman and it works like a charm.

Thanks again, all 3 of you, for the responses.