How to ignore letters in a formula

[IvanMD]

Hello,

I am creating a music chart list that contains numbers like 1, 2b, 3, 4, 5b....
I am using this formula to get the peak position in a range:

=MIN(L129:DE129)

It works when there is no "b" in the numbers, is there a way to tell it to ignore the "b"?

Thanks in advance for your help,

 

[shaowu459]

try this one, Ctrl+Shift+Enter for the array formula:
=MIN(IFERROR(1/SUBSTITUTE(L129:DE129,"b",)^-1,"@"))

 

[IvanMD]

OMG you guys are awesome! thanks for the quick response and solution. It works!

 

[shaowu459]

OMG you guys are awesome! thanks for the quick response and solution. It works!

you are welcome

 

[Dave Patton]

The nature and scope of your challenge is not clear.
Do you need the smallest or largest number?
How do 2 and 2b compare?
How many letters do you use?

An alternative to the suggestion that you received may be to custom format the cells that require a letter.
See below.
The custom format is like #"b"

T202006a.xlsm
	B	C
2	1	6
3	1	6
3a

Cell Formulas
Range	Formula
B2	B2	=MIN(IFERROR(1/SUBSTITUTE(L129:DE129,"b",)^-1,"@"))
C2	C2	=MAX(IFERROR(1/SUBSTITUTE(L129:DE129,"b",)^-1,"@"))
B3	B3	=MIN(L129:DE129)
C3	C3	=MAX(L129:DE129)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[IvanMD]

Hi Dave,

The music list I'm creating is based on the Billboard magazine charts, back when records used to have 2 sides, sometimes both sides would chart the same week and they would be listed as #, and #b for the flip side, but in some cases the flip side would stand alone and be listed just as #. I'm looking for a formula to determine the lowest number (peak position) in a range. The range could look like this: 10, 8, 8b, 5b, 3, 2b, 7, 10, where 2 is the peak position.

Thank you,

 

[Dave Patton]

Thanks for the information.
Either of the suggestions should work for you.
Neither suggestion differentiates between 2 and 2b.

T202006a.xlsm
	A	B	C	D	E	F	G	H	I	J
2		Peak
3	Numbers with text "b"	2	10	8	8b	5b	3	2b	7	10
4	Numbers with formatting	2	10	8	8b	5b	3	2b	7	10
3a

Cell Formulas
Range	Formula
B3	B3	=MIN(IFERROR(1/SUBSTITUTE(C3:J3,"b",)^-1,"@"))
B4	B4	=MIN(C4:J4)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[IvanMD]

Thank you so much! Great help.

 

[Dave Patton]

Thanks for the feedback.