Find lowest value between 2 text cells

Microsoft Excel 2003 (full product)
September 25, 2009 at 17:07:24
Specs: Windows XP
 I want to be able to return the lowest text value between a range of cells but am unable to work it out all help seems to only do this with numbers. e.g . cells may have A B C and i want it to return the lowest - A

See More: Find lowest value between 2 text cells

#1
September 25, 2009 at 18:19:04
 Check the =CODE() function,If cell A1 contains the text letter A in cell B1 enter =CODE(A1)Cell B1 will now give you the ASCII Code number for Capital Letter A, which is 65.Capital letter B is ASCII code 66.Using the good ol' ASCII code you can then get the lowest text value that your looking for.Just remember that in ASCII code the Capital letters are lower in number than the Lower Case letters.Then you could combine the =UPPER() function with the =CODE() function.MIKEhttp://www.skeptic.com/

Report •

#2
September 25, 2009 at 18:56:03
 Thanks - but is it not possible without having to use extra cells?

Report •

#3
September 25, 2009 at 19:06:18
 but is it not possible without having to use extra cells?You'll have to elaborate a bit, I'm not sure what you need done.Can you give an example?MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
September 25, 2009 at 19:13:53
 I would like to look at a range of cells e.g. C4:G:4 which each contain a differnt letter and return the lowest letter. without having to put values in for the letters. just like innumbers where you would use min or max.

Report •

#5
September 25, 2009 at 19:47:06
 Mike is right that you need to use the CODE function, but you don't need to use extra cells to do it.Assuming your letters are in A1:A5, array enter this formula using Ctrl-Shift-Enter:=INDEX(A1:A5,MATCH(MIN(CODE(UPPER(A1:A5))),CODE(UPPER(A1:A5)),0))Using Ctrl-Shift-Enter will add {} around the formula, making it an array formula.The UPPER is so you can mix your cases. You can leave it out if you only have uppercase or lower case.If you want to know how the formula works and can't figure it out on your own, come on back and I'll explain it.

Report •

#6
September 25, 2009 at 20:01:21
 THANKS BUT WHEN I TRIED THIS FORMULA IT COMES UP WITH THE n/a MESSAGE OR VALUE BUT DOESNT WORK - IDID FOLLOW YOUR INSTRUCTIONS AND COPIED THE FORMULA INTO THE CELL AND GOT THE { BRACKETS WHAT COULD I HAVE DONE WRONG?

Report •

#7
September 26, 2009 at 03:04:58
 I STILL COULDNT GET THAT TO WORK - I SORTED IT OUT IN A SLIGHT ROUND THE HOUSES WAY BY USING A VLOOKUP FUNCTION =VLOOKUP(MIN(CODE(A1:F1)),CODE_TRANSITION,2)WHERE I SET UP A RANGE "CODE_TRANSITION" USING THE CODE FORMULA. THIS IS WORKING IF I DO THE SHIFT CONTROL AND ENTER FOR THE ARRAY FORMULA - I HAVE NEVER USED ARRAY FORMULAS BEFORE - SO THANKS BOTH OF YOU FOR THE HELP.

Report •

#8
September 26, 2009 at 04:37:46
 Please do not post in all caps.I do not know what you did wrong since the formula I offered works fine for me.

Report •

#9
September 26, 2009 at 06:37:18

Report •

#10
September 26, 2009 at 08:13:01
 Try this:Place your target letters in Cells A1 - D1In Cell E1 enter the formula:=CHAR(MIN(CODE(A1),CODE(B1),CODE(C1),CODE(D1)))Should get you what you are looking for.MIKEhttp://www.skeptic.com/

Report •

#11
September 26, 2009 at 08:23:45
 Sorry about Caps - don't usually do that.Yeah that works - but I now have the problem - as I did with my own work around, that if one of the cells is blank it doesnt work - I want to use it for exam marks and they will often be blankand I will always be looking along rows and not up columns - so could you help me with that one? .

Report •

#12
September 26, 2009 at 08:47:30
 Hi,The Sort function puts empty cells at the end of the sorted list and doesn't come up with any errors.Regards

Report •

#13
September 26, 2009 at 08:58:11
 I dont want to sort them - I want to be able to return the lowest letter from a row of cells. The Char formula worked but if there were blanks it returned an error message. I need a work around for that please.

Report •

#14
September 26, 2009 at 12:35:15
 Hi,The following user defined function will return the smallest letter.Lower case are 'smaller' than upper case and blank cells and cells with a space in them are ignored.Numbers and any other characters are ignored.Enter the code in a standard module (not in PERSONAL.xls)In your worksheet enter the formula =AlphaSmall(L4:AJ4)Change L4:AJ4 to the range of cells with your letters inThis function has been setup to work on a single row of data onlyThere are some comments in the code, so you should be able to change it for slightly different logic.My test data consisted of 25 cells with two blank cells, one number and random lettersThe smallest letter was returned by the functionPublic Function AlphaSmall(rngSort As Range) As String ' Routine to return the text of the 'lowest' letter in a range of cells ' The sort order is Upper case A-Z, lower case a-z, lower case z being the smallest ' non-alpha cells are considered as 'larger' ' only single letters or the first letter in a cell are considered Dim varSortArray() As Variant Dim rngCell As Range Dim m As Integer Dim n As Integer On Error GoTo ErrHnd 'single row only If rngSort.Rows.Count > 1 Then GoTo ErrHnd 'size an array to match number of data elements ReDim varSortArray(rngSort.Columns.Count, 2) 'put data into array (as text) n = 1 For Each rngCell In rngSort.Cells varSortArray(n, 0) = rngCell.Text 'convert text to a numeric value If varSortArray(n, 0) = "" Then varSortArray(n, 1) = 0: GoTo NextCell If Asc(varSortArray(n, 0)) > 64 And Asc(varSortArray(n, 0)) < 91 Then varSortArray(n, 1) = Asc(varSortArray(n, 0)) - 64: GoTo NextCell If Asc(varSortArray(n, 0)) > 96 And Asc(varSortArray(n, 0)) < 123 Then varSortArray(n, 1) = Asc(varSortArray(n, 0)) - 69: GoTo NextCell 'sort anything else at the beginning varSortArray(n, 1) = 0: GoTo NextCell NextCell: n = n + 1 Next 'compare cells and create order numbers for each record For m = 1 To UBound(varSortArray, 1) For n = 1 To UBound(varSortArray, 1) If varSortArray(m, 1) >= varSortArray(n, 1) Then varSortArray(m, 2) = varSortArray(m, 2) + 1 Next n Next m 'get the largest order number n = 0 For m = UBound(varSortArray, 1) To 1 Step -1 For n = 1 To UBound(varSortArray, 1) If varSortArray(n, 2) = m Then AlphaSmall = varSortArray(n, 0) Exit Function End If Next n Next m AlphaSmall = CVErr(xlErrValue) Exit Function ErrHnd: AlphaSmall = CVErr(xlErrNA) End Function Regards

Report •

#15
September 26, 2009 at 21:08:10
 It is the =CODE() function that chokes on a blank cell and throws the #VALUE error up.A work around would be something like:=CHAR(MIN(IF(A1="",CODE("~"),CODE(UPPER(A1))),IF(B1="",CODE("~"),CODE(UPPER(B1))),IF(C1="",CODE("~"),CODE(UPPER(C1))),IF(D1="",CODE("~"),CODE(UPPER(D1)))))I used the tilde character because it's ASCII number is 126.I also added the =UPPER() function so even if you enter a lower case a it will be translated into an upper case.MIKEhttp://www.skeptic.com/

Report •

#16
September 27, 2009 at 07:08:32
 This was a good solution - The only problem with this is that if all the cells in the range are blank it returns the ~ symbol and I need them to be blank - can you help with this please

Report •

#17
September 27, 2009 at 07:37:33
 Hi,Wrap the formula you were given in an If statement.If it returns "~" then "" else the original formula=IF(CHAR(MIN(IF(A1="",CODE("~"),CODE(UPPER(A1))),IF(B1="",CODE("~"),CODE(UPPER(B1))),IF(C1="",CODE("~"),CODE(UPPER(C1))),IF(D1="",CODE("~"),CODE(UPPER(D1)))))="~","",CHAR(MIN(IF(A1="",CODE("~"),CODE(UPPER(A1))),IF(B1="",CODE("~"),CODE(UPPER(B1))),IF(C1="",CODE("~"),CODE(UPPER(C1))),IF(D1="",CODE("~"),CODE(UPPER(D1))))))Regards

Report •

#18
September 27, 2009 at 08:13:15
 Here is a solution for getting rid of the tilde:=CHAR(MIN(IF(A2="","160",CODE(UPPER(A2))),IF(B2="","160",CODE(UPPER(B2))),IF(C2="","160",CODE(UPPER(C2))),IF(D2="","160",CODE(UPPER(D2)))))You have to be careful in removing the data from the cells.You MUST delete the contents of the cell.If you simply Space over the letter, then the Space Character becomes the lowest ASCII number and that is what will be displayed.MIKEhttp://www.skeptic.com/

Report •

#19
September 27, 2009 at 08:29:32
 Here is a better solution, you no longer have to worry the space characters.=CHAR(MIN(IF(OR(A1="",A1=" "),"160",CODE(UPPER(A1))),IF(OR(B1="",B1=" "),"160",CODE(UPPER(B1))),IF(OR(C1="",C1=" "),"160",CODE(UPPER(C1))),IF(OR(D1="",D1=" "),"160",CODE(UPPER(D1)))))MIKEhttp://www.skeptic.com/

Report •

#20
September 27, 2009 at 10:39:04
 Hi,This solution allows you to use a range, rather than entering individual cell addresses.It copes with empty cells and cells with a single space in them.With data in A1 to D1enter this into any cell:=CHAR(MIN(CODE(UPPER(IF(ISBLANK(A1:D1),"{",IF(A1:D1=" ","{",(A1:D1)))))))then with your cursor in the formula bar and inside the formula enter ctrl+shift+enteri.e., press all three keys together.If you get this right the formula will be surrounded by { and }This is an array formula, but it can be dragged and copied like a normal formula.Regards

Report •

#21
September 27, 2009 at 16:16:23
 That was a good way - much easier to replicate

Report •

#22
September 27, 2009 at 16:47:53
 You're welcome

Report •

#23
September 28, 2009 at 00:13:31
 Thanks everyone for your help. I cant believe something which seems should be so simple would be so complicated. All of the techniques work but leave a sign -either { or1 or ~. But after i have replicated the foumula i wiil filter the symbols and delete them leaving the correct letter only.

Report •

#24
September 28, 2009 at 04:32:59
 Hi,With this minor change, a row of all empty cells returns an apparently empty cell.=CHAR(MIN(CODE(UPPER(IF(ISBLANK(A1:D1),CHAR(160),IF(A1:D1=" ",CHAR(160),(A1:D1)))))))Character 160 prints as nothing in the standard character setAs before it is an array formula - Ctrl + Shift + EnterRegards

Report •