# Identifying if a specific Text is found as part of the text in a cell

#### Mozzz

##### Board Regular
I am trying to determine if "UD" is found in a range of cells. So if the text "11 UD 2000" was in the text then the variable UDFound is true. The spacing of the UD could change so I need it test the entire string in the cell.
Here is what I have :
Code:
``````Sub CalculateFloorPlanDetails()
'
' CalculateFloorPlanDetails Macro
' Totals International1067 Form into New / Used Interest - New / Used Flat Fee
'
Cells.UnMerge
FirstColumn = Cells(14, Columns.Count).End(xlToLeft).Column
FirstRow = Cells(1, 18).End(xlDown).Row
FinalRow = Cells(Rows.Count, 16).End(xlUp).Row
For i = FirstRow + 2 To FinalRow
If (Len(Cells(i, 5))) = 17 Then
If Cells(i, 4).Value = "*UD*" Then
TotalUD = TotalUD + Cells(i, 18)
TotalUDCount = TotalUDCount + 1
Else

TotalNew = TotalNew + Cells(i, 18)
TotalNewCount = TotalNewCount + 1
End If
Else

TotalUsed = TotalUsed + Cells(i, 18)
TotalUsedCount = TotalUsedCount + 1
End If

Next i

MsgBox "Total New Amount is " & TotalNew _
& " and Total Count is " & TotalNewCount

MsgBox "Total UD Amount is " & TotalUD _
& " and Total Count is " & TotalUDCount
MsgBox "Total Used Amount is " & TotalUsed _
& " and Total Count is " & TotalUsedCount

End Sub``````

It just goes by the UD test and gives me the new and used.
Here is a small sample of the data:
<TABLE style="WIDTH: 585pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=779><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>PROSTAR + 6X4L</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>1HSDJSJR3CJ611511</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>7/27/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>10/25/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>8/1/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>PROSTAR + 6X4L</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>1HSDJSJR5CJ611512</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>7/29/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>10/27/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>8/1/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>11 UD 2600R</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>JNAA410H5BAR10179</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>12/13/2010</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>12/15/2010</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>1/1/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>11 UD2000 N</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>JNAL310H5BAN10022</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>2/23/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>2/28/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>12/1/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>11 UD2000 N</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>JNAL310H6BAN10031</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>5/12/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>5/16/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>12/1/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125>17</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>11 UD2000 N</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>JNAL310H9BAN10041</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>5/12/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>5/16/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>12/1/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>04 INT 4400 REG CA</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>G84354J017686</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>7/29/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>7/29/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>2/1/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69>2076</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>05 INT 9400i6X4</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>G84155C052272</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>8/31/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>8/31/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>4/1/2012</TD></TR></TBODY></TABLE>

Thanks,

Mozzz

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Michael M

##### Well-known Member
Umm,
where does the 17 and the 2076 come from ??
New and used what ??
Should the UD count be 4 ?

#### b.downey

##### Active Member
Try using int "instr" function...

For example
Code:
``If instr(Cells(i, 4).Value , "UD") > 0 then``

#### Mozzz

##### Board Regular
The report specifies the entire vin # for the vehicle if it is new, thus Len = 17 catches the new. The used vehicles are less then 17. The manufacturer Nissan UD New (Len = 17) needs to be separated from the rest of the New. The count you see of course would be 4. This is just a small portion of the report. I am solving for count and amount. Manually this takes about 10 minutes to do. Should be able to get it done in seconds once I solve this problem.

I need the amounts to add if it is new UD separately from new everything else. The best way to do this would be if I could use the if statement to see if the cell has "UD" anything in it. The report is posted to our accounting department as New International, New UD and Usd Truck Floor Plan Interest.

Don't see a 2076, maybe you mean 1067 which is the report number.

#### Mozzz

##### Board Regular
Instr Works perfect. Thanks,
Mozzz

Replies
5
Views
932
Replies
13
Views
1K
Replies
12
Views
391
Replies
3
Views
343
Replies
3
Views
328

1,127,752
Messages
5,626,661
Members
416,199
Latest member
Gautamsunil

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