VBA get max value from right of cells in range

[dave2018]

Hi, I'm writing this from a cellphone so I can't upload the data, but I have a column which has the following values (I will type this in array format, without the curly brackets): Bunk1;Bunk1;Bunk2;Bunk8;Bunk5;Camper;Bunk6;Bunk6;Nurse;Bunk7

I need the maximum value of a bunk, in this case the number 8, returned through a sub or function.

I don't think any values other than the ones starting with bunk will have numbers, such as nurse1, but I need to anticipate that, so the code gets the max of only the bunks.

Keep in mind that bunk10 may exist, so u can't just extract the right-most value. There may be double digits.

Thanks all!

 

[Peter_SSs]

I wasn't sure if you really need vba or whether you thought that was the only/best way?
Is this worksheet formula any use?

Excel Workbook
	A	B
2	Bunk6	23
3	Bunk
4	Nurse88
5	Bunk23
6	Bank5000
7	200
8
9
Max

 

[dave2018]

Hi, I do need vba. It's part of a bigger sub that I'm working on. Thx.

 

[Peter_SSs]

Hi, I do need vba. It's part of a bigger sub that I'm working on. Thx.

Then see if you can extract what you want from this:

Sub MaxWithText()
  Dim TextMax As Double
  
  Const LeftText As String = "Bunk"
  
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    TextMax = Evaluate(Replace(Replace("aggregate(14,6,replace(#,1,4,"""")/(left(#,4)=""@""),1)", "#", .Address), "@", LeftText))
  End With
  MsgBox TextMax
End Sub