thank you for the response.Does it have to be with vba?
Can you show us how the result(s) should be shown?
Better still, can you show us a variety of input data (it sounds like sometimes it might not all be numeric), preferably with XL2BB so we can copy/paste to test with and include the expected results?
<table style="width:100%">
<tr>
<th>prodeuctName</th>
<th>productNo</th>
</tr>
<tr>
<td>s12-12-123</td>
<td>12-12-123</td>
</tr>
<tr>
<td>s12-12-124</td>
<td>12-12-124</td>
</tr>
</table>
Cell Formulas | ||
---|---|---|
Range | Formula | |
A2:A5 | A2 | =IFERROR(TRIM(MID($A$1,FIND("|",SUBSTITUTE("-"&$A$1&"-","-","|",ROW()-ROW($A$2)+1)),FIND("|",SUBSTITUTE(" "&$A$1&"-","-","|",ROW()-ROW($A$2)+1))-FIND("|",SUBSTITUTE("-"&$A$1&"-","-","|",ROW()-ROW($A$2)+1))-1)),"") |
B2:B5 | B2 | =IFERROR(ISNUMBER(NUMBERVALUE(TRIM(MID($A$1,FIND("|",SUBSTITUTE("-"&$A$1&"-","-","|",ROW()-ROW($A$2)+1)),FIND("|",SUBSTITUTE(" "&$A$1&"-","-","|",ROW()-ROW($A$2)+1))-FIND("|",SUBSTITUTE("-"&$A$1&"-","-","|",ROW()-ROW($A$2)+1))-1)))),"") |
sorry i mean to say, check if column (product no) contains product name.Unfortunately, as you can see, you cannot show sample data like that.
Also, this does not make sense to me:
This is similar to what I really what to do.if possible could you help me to do it in vba. Thank you very much.
Cell Formulas Range Formula A2:A5 A2 =IFERROR(TRIM(MID($A$1,FIND("|",SUBSTITUTE("-"&$A$1&"-","-","|",ROW()-ROW($A$2)+1)),FIND("|",SUBSTITUTE(" "&$A$1&"-","-","|",ROW()-ROW($A$2)+1))-FIND("|",SUBSTITUTE("-"&$A$1&"-","-","|",ROW()-ROW($A$2)+1))-1)),"") B2:B5 B2 =IFERROR(ISNUMBER(NUMBERVALUE(TRIM(MID($A$1,FIND("|",SUBSTITUTE("-"&$A$1&"-","-","|",ROW()-ROW($A$2)+1)),FIND("|",SUBSTITUTE(" "&$A$1&"-","-","|",ROW()-ROW($A$2)+1))-FIND("|",SUBSTITUTE("-"&$A$1&"-","-","|",ROW()-ROW($A$2)+1))-1)))),"")
View attachment 27558
I'm guessing you actually mean that the other way around. That is "check if product name contains product no."check if column (product no) contains product name.
Sub NumberInName()
Dim a As Variant, b As Variant
Dim i As Long
a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a), 1 To 1)
For i = 1 To UBound(a)
b(i, 1) = IIf(InStr(1, a(i, 1), a(i, 2), 1) > 0, "Yes", "No")
Next i
Range("C2").Resize(UBound(b)).Value = b
End Sub
dummies.xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | product name | product no. | Product no. in product name? | ||
2 | s12-12-123 | 12-12-123 | Yes | ||
3 | s12-12-124 | 15-19-195 | No | ||
Sheet1 |
thank you so much.I'm guessing you actually mean that the other way around. That is "check if product name contains product no."
If so, you could try this with a copy of your data.
VBA Code:Sub NumberInName() Dim a As Variant, b As Variant Dim i As Long a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value ReDim b(1 To UBound(a), 1 To 1) For i = 1 To UBound(a) b(i, 1) = IIf(InStr(1, a(i, 1), a(i, 2), 1) > 0, "Yes", "No") Next i Range("C2").Resize(UBound(b)).Value = b End Sub
Here is my sample data and results
dummies.xlsm
A B C 1 product name product no. Product no. in product name? 2 s12-12-123 12-12-123 Yes 3 s12-12-124 15-19-195 No Sheet1