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

Topic

New Reply

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.

Reply | Quote

Replies

To eliminate “*” you can find “~*” and replace with nothing.

Reply | Quote

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.

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

Superscript maybe?

Reply | Quote

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

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

it returns a CHAR(43) which is a regular plus sign. even if i make it a superior plus sign it the =code(A1) still gives me a 43, so both are a 43, regular or superior.

i guess it can only be done thru vba

Reply | Quote

If you are superscripting the Plus, the character is still the plus, it is just sperscripted. Some fonts have “superscripted characters” that are different , eg Code 178 is a “superscripted 2” and is NOT a 2 =Code50.

I do NOT think that a “Supercripted +” is a character in a normal font, so it must be superscripted via a macro or manually. It can NOT be done in a formula!
Steve

Reply | Quote

I’m assuming then that when you say ‘superior’ you’re referring to what everyone else here calls superscript. In that case, you do indeed need a macro and one like Steve posted should do fine.

Be aware though that, because you can’t superscript a character returned by a formula, the only way to superscript an individual character in such a cell is by converting its contents into an alpha-numeric string. That means that by running the superscripting macro you lose the formula, if the cell has one.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

See if the attachment to post 144039 is of use, and note the underlying VBA method to produce unicode, courtesy of Rory. OTOH, it may be useless .

Reply | Quote

I assume by “superior +” you mean a “superscripted Plus sign” I know of know character set that has a super+.

If you just have the “500*” entered and you want to change the asterisk to a superscripted + try this code. Highlight the cells to check and it will change all the ending Asterisks to superscripted +.

Steve

Sub AsteriskToSuperiorPlus()
    Dim rCell As Range
    Dim iLen As Integer
    For Each rCell In Selection
        If Right(rCell.Value, 1) = "*" Then
            iLen = Len(rCell.Value)
            rCell.Value = Left(rCell.Value, iLen - 1) & "+"
            rCell.Characters(Start:=iLen, Length:=1).Font.Superscript = True
        End If
    Next
End Sub

Reply | Quote

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(strCellVal, 1) = “*” Then
rngCell.Value = Left(strCellVal, intVLen – 1)
End If
End If
Next rngCell
End Sub

Reply | Quote

worked great! thanks much

Reply | Quote

Found a coding “error” which would slow it a little; see edit to the post in red.

Reply | Quote

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

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

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

Reply | Quote

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.

Reply | Quote