Find the maximum or large of a string ID

[kelly mort]

Say I have

CPI_101
CPI_109

And more as IDs in my database . Can I use vba to find the largest number part?

It's looking trick for me.

Thanks

 

[Fluff]

If they are all the same format, you don't need VBA

Excel 2013/2016
	A	B
1	CPI_101	109
2	CPI_109
3	CPI_100
4	CPI_099
5	CPI_106
Cover

Cell Formulas
Range	Formula
B1	{=MAX(--RIGHT(A1:A5,3))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kpasa]

If they are all the same format, you don't need VBA

I agree with this assessment. And his formula is just one of many very efficient ways to do so.

 

[JoeMo]

Say I have

CPI_101
CPI_109

And more as IDs in my database . Can I use vba to find the largest number part?

It's looking trick for me.

Thanks

Here's a UDF you can try. After you install it, you can use it like a worksheet function - see the example below. It assumes your Part IDs always have the format: string_number. You must have at least one cell in the range input that has this format or the UDF will return an error (#VALUE!).

Function LargestPartID(R As Range) As String
Dim V As Variant, i As Long, x As Double, MaxVal As Double, Prefix As String
V = R.Value
For i = 1 To UBound(V, 1)
    If InStr(V(i, 1), "_") > 0 Then
        x = Replace(V(i, 1), Mid(V(i, 1), 1, InStr(V(i, 1), "_")), "")
        If x > MaxVal Then
            MaxVal = x
            Prefix = Mid(V(i, 1), 1, InStr(V(i, 1), "_"))
        End If
    End If
Next i
If MaxVal > 0 Then
    LargestPartID = Prefix & MaxVal
Else
    LargestPartID = CVErr(xlErrValue)
End If
End Function

Excel Workbook
	A	B	C
1	Part ID		Largest
2	CPI_101		XYZ_117
3	CPI_109
4
5	XYZ_117
6	CPI_100
Sheet4

 

[kelly mort]

If they are all the same format, you don't need VBA

Excel 2013/2016

	A	B
CPI_101
CPI_109
CPI_100
CPI_099
CPI_106

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]109[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

</tbody>

Cover​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]{=MAX(--RIGHT(A1:A5,3))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Okay cool.

Can we use the "CPI_" to determine how many characters at the right?

By taking the length of "CPI_" from it then use the rest as the right character.

Because they will not all have same number of characters.

Thanks

 

[Fluff]

As long as there is only one _ in the string you could use

Excel 2013/2016
	A	B
1	CPI_101	1001
2	CPI_109
3	CPI_1001
4	CPI_99
5	CPI_11
Cover

Cell Formulas
Range	Formula
B1	{=MAX(--RIGHT(A1:A5,LEN(A1:A5)-FIND("_",A1:A5)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Or, in code

Sub kelly_mort()
   Dim x As Variant
   Dim Addr As String
   Addr = Range("A1", Range("A" & Rows.count).End(xlUp)).Address
   x = Evaluate("max(--right(" & Addr & ",len(" & Addr & ")-find(""_""," & Addr & ")))")
End Sub

Or use JoeMo' code as that allows for blank cells

 

[kelly mort]

@JoeMo,

Your udf is sweet. Thanks

@Fluff,

Your second formula is very cute.

Thanks to yourself

 

[Fluff]

Glad we could help & thanks for the feedback

 

[kelly mort]

Or, in code

Sub kelly_mort()
   Dim x As Variant
   Dim Addr As String
   Addr = Range("A1", Range("A" & Rows.count).End(xlUp)).Address
   x = Evaluate("max(--right(" & Addr & ",len(" & Addr & ")-find(""_""," & Addr & ")))")
End Sub

Or use JoeMo' code as that allows for blank cells

Yes I observed that blank cell issue with your formula.