Consulting

Results 1 to 11 of 11

Thread: Delete all characters after a set number in a cell

  1. #1
    VBAX Regular
    Joined
    Apr 2009
    Posts
    9
    Location

    Delete all characters after a set number in a cell

    Hi all,

    I have some cells that have the following data:

    A1 = ABC/01/1234 Testing Stuff
    A2 = ABS/09/1345 Other Stuff

    I would like to remove the / and replace with a space which I have done by -

    [VBA]
    Sub removeText()
    Selection.Replace "/", " "
    End Sub
    [/VBA]

    This gives me:

    A1 = ABC 01 1234 Testing Stuff
    A2 = ABS 09 1345 Other Stuff

    I now need to delete all after the final number.

    I was thinking of using Len but never used it before as I am just learning. Any ideas are very welcome.

    Regards

    K

  2. #2
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Is there always 11 characters (including spaces) before the stuff at the end?
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  3. #3
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    The Len function returns the length, the number of characters, in a string.

    For the two examples you provided, you indicate that you want to keep the first 10 characters ("ABC 01 1234" is 10 characters log, including spaces).

    You could use the Left function to grab the first 10 characters:
    [VBA]Left("ABC 01 1234 Testing Stuff", 10)[/VBA]
    which will return: "ABC 01 1234"

    Now, using the Left function will get tricky if what you want ever exceeds 10 characters, because you will then need to determine how to identify only what you want, which shouldn't be too hard if your data is consitent.

    Your original data shows as "ABC/01/1234 Testing Stuff", which shows the data you want with a space separating the rest of the entry. If this is constant, then you could easily identify just the first section of the data using the InStr function, which looks for the position of an identified string or character within a larger string.

    So, you could use this to pull out just what you want:
    [VBA]Left("ABC/01/1234 Testing Stuff", InStr(1, strtest, " ") - 1)[/VBA]

    This will return "ABC/01/1234". The '-1' is there becase, without it, you would have an extra space at the end of your returned value.

    Now you could use the Replace function to replace the / with a space.

    Be sure to lookup the specifics on the functions in Help.

    HTH
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Worksheet formula
    =SUBSTITUTE(LEFT(A1,FIND(" ",A1)),"/"," ")
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Regular
    Joined
    Apr 2009
    Posts
    9
    Location
    Hi all, it will always be the 11.

    Will check the details at work tomorrow.

    Many thanks everyone.

    K

  6. #6
    VBAX Regular
    Joined
    Apr 2009
    Posts
    9
    Location
    Hi all,

    I am looking at using the cell that is highlighted rather than the actual characters.

    I know I can do it with =Left(C1,10) but I am having trouble with the information that has been posted aready.

    Is there a way that I can do that in VBA?

    I may be barking up teh wrong tree, but I am learning

    K

  7. #7
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Greetings Karti,

    I think from looking at your first post you are referring to using the selection. Presuming you're not selecting a bajillion cells or anything (this code's not particularly efficient), would this work?

    [vba]
    Option Explicit

    Sub removeText()
    Dim rCell As Range
    Dim LoopAgain As Boolean

    Selection.Replace "/", " "
    For Each rCell In Selection
    Do
    If Not Right(rCell.Value, 1) Like "#" Then
    LoopAgain = True
    rCell.Value = Left(rCell.Value, Len(rCell.Value) - 1)
    Else
    LoopAgain = False
    End If
    Loop While LoopAgain = True
    Next
    End Sub
    [/vba]

    Basically this strips characters from the right until it gets a number.

    Mark

  8. #8
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    ACK! That would foul in case you had an empty cell...

    [vba]
    Sub removeText()
    Dim rCell As Range
    Dim LoopAgain As Boolean
    Selection.Replace "/", " "
    For Each rCell In Selection
    Do
    LoopAgain = False
    If Len(rCell.Value) = 0 Then Exit Do

    If Not Right(rCell.Value, 1) Like "#" Then
    LoopAgain = True
    rCell.Value = Left(rCell.Value, Len(rCell.Value) - 1)
    Else
    LoopAgain = False
    End If
    Loop While LoopAgain = True
    Next
    End Sub
    [/vba]

  9. #9
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    [VBA]Sub Test_Keep11ReplaceSlash()
    [A1] = "ABC/01/1234 Testing Stuff"
    [A2] = "ABS/09/1345 Other Stuff"

    [A4] = "Test"
    [A1:A4].Select

    Keep11ReplaceSlash
    End Sub

    Sub Keep11ReplaceSlash()
    Dim cell As Range
    For Each cell In Selection
    If Len(cell.Value) > 10 Then cell.Value = Left(Replace(cell.Value, "/", " "), 11)
    Next cell
    End Sub[/VBA]

  10. #10
    VBAX Contributor
    Joined
    Jul 2004
    Location
    Gurgaon, India
    Posts
    148
    Location
    Hi,

    [vba]Sub kTest()
    Dim r As Long
    Application.ScreenUpdating = 0
    r = Range("a" & Rows.Count).End(xlUp).Row
    Columns(1).Insert
    With Range("a1")
    .Resize(r).FormulaR1C1 = "=left(rc[1],find("" "",rc[1]&"" "")-1)"
    .Offset(, 1).Resize(r).Value = .Resize(r).Value
    End With
    Columns(1).Delete
    Application.ScreenUpdating = 1
    End Sub[/vba]

    HTH

  11. #11
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Similar methodology to Krishna. We are told the string length so
    [VBA]
    Sub trims()
    Dim rng As Range
    Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    With rng
    .Offset(, 2).FormulaR1C1 = "=left(RC[-2],11)"
    .Offset(, 2).Copy
    .PasteSpecial Paste:=xlPasteValues
    .Offset(, 2).ClearContents
    .Range("A1").Select
    End With
    End Sub
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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