Changing text string to number?

dm59

Board Regular
Joined
Jul 13, 2006
Messages
73
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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
what happens if you do a find/replace on the column and replace ft2 with a null (blank)?
 

mooseman

Board Regular
Joined
Jul 23, 2004
Messages
195
Replace the column letter with whatever you need. add this to your macro code.

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


And for the formatting, i think the code you want is something like this:
Code:
Columns("A:A").Numberformat = "#,##0"

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

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, This will replace in selection.
Code:
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

Board Regular
Joined
Jul 13, 2006
Messages
73
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

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