Results 1 to 6 of 6

Removing text from a number

This is a discussion on Removing text from a number within the Excel Questions forums, part of the Question Forums category; Could someone tell me how to remove the text from a number .eg remove the s from 3458.0s The text ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    Australia
    Posts
    58

    Default

    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

  2. #2
    AJ
    AJ is offline
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478

    Default

    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

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    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

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Australia
    Posts
    58

    Default

    AJ thanks for the quick reply.

    That code works perfectly

    Regards

    Mark

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Location
    Australia
    Posts
    58

    Default

    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

  6. #6
    Board Regular
    Join Date
    Jun 2002
    Location
    Australia
    Posts
    58

    Default

    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


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com