# Is it possible to find a certian character in a column if cells?

#### DeezNuts

##### Board Regular
Curious how would one go about editing something this
Code:
=SUMIF(\$D\$6:D8000,">=0", \$E\$6:E8000)

So if any of the cells which are normally 10.00 had a value of 10% the formula would also search for the % character and ignore that cell if one is found. Is this possible?

I have tried =ISNUMBER(SEARCH but only works for me if the % is by itself if its 25% wont work

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
The percent symbol when it's with a number is generally a formatting thing (i.e. the % symbol is not actually there). If you are trying to find numbers that are percentages you can look for numbers smaller than one.

After trying a few things I was kinda leaning that way that it cant be done. Thank you for the assistance. losing my mind trying to find a work around to a minor (major) issue
Code:
[TABLE="width: 648"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=SUM(IF(1-ISNUMBER(SUBSTITUTE(XEM1048551,"%","")+0),1))[/TD]
[/TR]
[TR]
[TD]=IF(COUNT(SEARCH("~"&RIGHT(XEM1048552,1),"%")),"Y", "N")
[/TD]
[/TR]
[TR]
[TD]=--(LEN(XEM1048552)>LEN(SUBSTITUTE(XEM1048552,"%","")))[/TD]
[/TR]
[TR]
[TD]=--NOT(ISERROR(FIND("%",XEM1048552)))[/TD]
[/TR]
[TR]
[TD]=IF(SEARCH("%",XEM1048552&"%"),"",SUMIF(\$D\$6:D8000,">=0",\$E\$6:E8000))[/TD]
[/TR]
[TR]
[TD]=IF(ISNUMBER(SEARCH("%",XEM1048552)),"Y", "N")
[/TD]
[/TR]
[TR]
[TD]=IF(SUBSTITUTE(XEM1048552,"%","")<>XEM1048552,"Y","N")[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

You could try this user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Rich (BB code):
Function SumNoPercent(r As Range) As Single
Dim c As Range

For Each c In r
If Right(c.Text, 1) <> "%" Then SumNoPercent = SumNoPercent + c.Value
Next c
End Function

Excel Workbook
B
1
210
310%
40.056
50.03%
620
740
8
970.056
SUM Excluding %

That Sir is very cleaver thank you. That works great.

Have a question is it possible to format certain columns (more so every third cell in certain columns) using vba? So for instance in my form I put any number without a % it formats as general but if I input a number with a % it format the cell as percent? Currently when the form tries to put say 90% on the sheet it makes 90.00. Just curious trying to think of a way to repair a design flaw is all.

That Sir is very cleaver thank you. That works great.

.. is it possible to format certain columns (more so every third cell in certain columns) using vba?
Yes. However, I didn't fully understand the rest of your explanation. To provide suggested code I would nee to know
- which column?
- starting at which row?
- where do we finish doing this? That is, how many rows should be done? Or what is the finishing row?
- what is the formatting of the majority of cells to be? That is, the first two of each group.
- what is the formatting of the third cell in each group to be?
- when is the formatting to be applied? Before/after data is entered.

Honestly I am terrible at trying to explain things but I will give it a go using the actual code.

Code:
Private Sub cmdSubmit_Click()
Application.ScreenUpdating = False
Dim NextRw As Long
Dim ws As Worksheet
Dim startRow As Long

Set ws = Courses
NextRw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
If NextRw < 6 Then NextRw = 6
'<< Add data to worksheet >>
ws.Cells(NextRw + 0, "A") = Me.txtAssignmentName.Value
ws.Cells(NextRw + 1, "A") = Me.txtDate.Value
ws.Cells(NextRw + 2, "A") = Me.txtAssignmentType.Value
If (Me.txtPointsReceived.Value & "X" = "X") Then
ws.Cells(NextRw + 0, "D").Value = "-"
Else
ws.Cells(NextRw + 0, "D").Value = Me.txtPointsReceived.Value '< If value contains % format cell for percent else if no % format as number
'< (ex. 20.00) prior to inputting the value
End If
ws.Cells(NextRw + 0, "E") = Me.txtPointsPossible.Value '< If value contains % format cell for percent else if no % format as custom (ex. "/" 0.00)
'< prior to inputting the value

End Sub

Only two possible value input styles one style would be say 90% out of 100% and the other would be 10 out of 10. Is this achievable with vba?

Not sure I have all the specific detail and I don't have the UserForm or data to test, but see if this is headed in the right direction - from which you might be able to tweak it to your specific needs.
Rich (BB code):
If (Me.txtPointsReceived.Value & "X" = "X") Then
ws.Cells(NextRw + 0, "D").Value = "-"
Else
ws.Cells(NextRw + 0, "D").Value = Me.txtPointsReceived.Value '< If value contains % format cell for percent else if no % format as number
'< (ex. 20.00) prior to inputting the value
End If
ws.Cells(NextRw + 0, "E") = Me.txtPointsPossible.Value '< If value contains % format cell for percent else if no % format as custom (ex. "/" 0.00)
'< prior to inputting the value
Try replacing the above code with that below.
You'll also an extra Dim statement to Dim CelFormat as String

Rich (BB code):
If (Me.txtPointsReceived.Value & "X" = "X") Then
ws.Cells(NextRw + 0, "D").Value = "-"
Else
If InStr(1, txtPointsReceived, "%") = 0 Then
CelFormat = "0.00"
Else
CelFormat = "0.00%"
End If
With ws.Cells(NextRw + 0, "D")
.NumberFormat = CelFormat
End With
End If
If InStr(1, txtPointsPossible, "%") = 0 Then
CelFormat = """/"" 0.00"
Else
CelFormat = "0.00%"
End If
With ws.Cells(NextRw + 0, "E")
.NumberFormat = CelFormat
.Value = Me.txtPointsPossible.Value
End With
Note too that those red "+ 0" bits are not required, but don't do any harm.

Peter, thank you so much, that works beautifully. I appreciate all your work on this.

Peter, thank you so much, that works beautifully. I appreciate all your work on this.
No problem, glad it worked for you.

Replies
1
Views
249
Replies
1
Views
249
Replies
1
Views
443
Replies
8
Views
510
Replies
1
Views
371

1,207,205
Messages
6,077,045
Members
446,252
Latest member
vettaforza

### 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.

### Which adblocker are you using?

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

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