Removing text from a number

Mark Gilks

Board Regular
Joined
Jun 30, 2002
Messages
58
Could someone tell me how to remove the text from a number .eg remove the s from 3458.0s

The text is corrupting the data for a chart.

Regards

Mark
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
If the text character is always the last character in the cell then this formula would do it...

=LEFT(A1,LEN(A1)-1)

There's always the option though of downloading asap-utilities from http://www.asap-utilities.com as one of the menu functions this adds is
"Delete all text characters in selection"
How this works though, I don't know!

Rgds
AJ
 
Upvote 0
Hi,

You could use a worksheet formula, using IF, LEFT, and LEN. This should work if you have a consistent pattern, like only needing to drop the last letter.

You could use VBA to do it in one shot to all the items in a selected range.

Code:
Sub test()
Dim cell As Range

For Each cell In Selection
    If Not cell.HasFormula And Not IsEmpty(cell) Then cell = Val(cell)
Next cell

End Sub

A question, though: How did the data come to be that way? Is this the result of a formula (you will need to drop the .HasFormula part of the code above)? Or is this how your data is retrieved?

Bye,
Jay
 
Upvote 0
Jay,

The data is retrieved from a query. The letter is only there at certain times most of the time it is just a number.

I am very new to VBA but will have go at your code.

Thank you very much
 
Upvote 0
Jay, I've included your code within my macro for retrieving the data and it works very well.
Would any body know the VBA code to remove any spaces before the data within a selected range?



Sub RefreshData()
'
' RefreshData Macro'
Application.ScreenUpdating = False
Sheets("Data").Select
Range("A2:R200").Select
Selection.ClearContents
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("D32:O200").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 1
.PatternColorIndex = xlAutomatic
End With
Rows("2:200").Select
Selection.RowHeight = 12
Columns("E:L").Select
Range("E2").Activate
Selection.ColumnWidth = 6
Range("M1:O200").Select
Selection.ClearContents

'Jay's macro for deleting text'

Dim cell As Range
Range("h3:h200").Select
For Each cell In Selection
If Not cell.HasFormula And Not IsEmpty(cell) Then cell = Val(cell)
Next cell



Sheets("LivePricePage").Select
Application.ScreenUpdating = True
End Sub

Regards

Mark Gilks
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top