Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Take out the asterisk (*) in a cell (Excel xp/win 2000)

    I need to search through a group of cells. Some have an asterisk in them... 53*. I need to delete the asterisk and keep the value in the same cell, I just need the 53 not the 53*. How can I code this in vba? Thanks for the help.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    To eliminate "*" you can find "~*" and replace with nothing.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    John told you how to do this using Find/Replace. If you really need to do it using VBA, the following will remove the first Asterick in all the cells in the current selection. If there can be more than one astherick, or you need some other range that the current selection, it will need to be modified.

    <pre>Dim oCell As Range
    Dim iPos As Integer
    For Each oCell In Selection
    iPos = InStr(oCell.Value, "*")
    If iPos > 0 Then
    oCell.Value = Left(oCell.Value, iPos - 1) & Right(oCell.Value, Len(oCell.Value) - iPos)
    End If
    Next oCell
    </pre>

    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    worked great! thanks much

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    Legare correctly pointed out that I didn't answer your request for VBA code. In addition to his code here's a couple more VBA bits, both require that you select the cells to be operated on before running the macro:

    Sub RemoveAllAsterisks()
    On Error Resume Next
    Selection.Replace What:="~*", Replacement:=""
    End Sub

    This one removes only trailing (last in cell contents) asterisks.

    Sub RemoveTrailingAsterisk()
    Dim rngCell As Range
    Dim strCellVal As String
    Dim intVLen As Integer
    For Each rngCell In Selection
    strCellVal = rngCell.Value
    If InStr(strCellVal, "*") > 0 Then
    intVLen = Len(strCellVal)
    If Right(<font color=red>strCellVal</font color=red>, 1) = "*" Then
    rngCell.Value = Left(strCellVal, intVLen - 1)
    End If
    End If
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    Found a coding "error" which would slow it a little; see edit to the post in red.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    John,

    Since my Excel VBA isn't all that great, let me ask the following question with respect to the following part of your code

    <pre> If InStr(strCellVal, "*") > 0 Then
    intVLen = Len(strCellVal)
    If Right(strCellVal, 1) = "*" Then
    rngCell.Value = Left(strCellVal, intVLen - 1)
    End If
    End If
    </pre>


    If the 1st If is not successful (ie, no asterisks in cell), next 5 statements skipped. No problem. If there is one or more *'s in the cell, there may be one in the last position (regardless of whether there are any prior to that in the cell). So why is the first If-test even needed and the following intVLen=...? Why not just do the If Right test since that would have to be done if there's an * anywhere with the code as it exists? Then the rngCell.Value statement could just be:
    rngCell.Value = Left(strCellVal, Len(strCellVal) - 1)

    Thanks for any insights.

    Fred

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    Fred, you are right. The first 'if' is only intended to speed the code by not setting the additional variable or testing the location of the asterisks.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    There is no way with a formula to change the contents of a cell to something else. If A1 = 500* and you want B1 to be 500+ then this type of formula in B1 will work:

    =IF(RIGHT(A1,1)="*",LEFT(A1,LEN(A1)-1)&"+")

    If you want A1 to change from 500* to 500+ then you need a macro or find/replace [find: ~*/replace:+]

    Steve

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    is it possible to put this in a formula in excel? I need to check if another cell has a number with an asterisk at the end. i need to change the asterisk to a superior plus sign and leave the number in. in other words, if it is 500* i need to change it to 500+. thanks

    also, in another instance i need to see if a + sign is at the end of a cell's contents i need to put it in the outside of the parens in another cell. so if A10 has 400+ in it, i need to make b10 (400)+. thank you for the help

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    thank you, this worked great.

    I'm trying to make a superior plus +, i've tried copying to the formula but it will not paste in a superior form. Do i have to use something like CHAR(00)? how do i know the character number of a superior plus sign? thank you

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    Hi jha,

    I don't know what a "superior plus" is, but if you type one into a cell, say A1, and put =code(A1) into another cell, that will return the character's value. As for changing the asterisk to a +, try:
    =SUBSTITUTE(A1,"*","+")
    This will replace every "*" in a cell with "+". If you want to replace only the first asterisk, use:
    =SUBSTITUTE(A1,"*","+",1)
    or, to replace the second asterisk, use:
    =SUBSTITUTE(A1,"*","+",2)
    and so on.

    To replace the asterisk with your "superior plus", just change the "+" to CHAR(#), where # is the number returned by the CODE(A1) formula referred to earlier. Note too that the "*" and "+" arguments in the SUBSTITUTE formula can be more than one character long, and do not need to be the same length. For example you could have:
    =SUBSTITUTE(A1,"*",CHAR(33)&"+",1)

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    Superscript maybe?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    Possibly. I was wondering about Char(134), Char(135) and Char(177) too.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Take out the asterisk (*) in a cell (Excel xp/win 2000)

    I think Char(177) is known as Plus-Minus, whilst the other pair are known as Dagger and Double Dagger respectively. No idea about superior plus.

    Andrew

Page 1 of 2 12 LastLast

Posting Permissions

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