Microsoft Excel 2003 (full product)

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

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 valuethat 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.MIKE

Thanks - but is it not possible without having to use extra cells?

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?

MIKE

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.

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 enterthis 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.

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?

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.

Please do not post in all caps. I do not know what you did wrong since the formula I offered works fine for me.

Hi, You can use the Sort on the Data menu

This treats upper and lower case as the same letter by default, but read on ...

Here is my starting list on the left and sorted list on the right

a a C C g E h g Y G O h p H y j E k w O T p G T k w j Y H yTo use the built-in sort, select the range of cells to be sorted then goto Data - Sort

The box offers options such as whether your columns of data have headings - if so it leaves them as headings and doesn't sort them, and if you have selected more than one column of data you can sort on one column or more than one column.If you need to sort both upper and lower case you can create a custom list with A to Z all upper case followed by a-z all lower case.

Create a column of these 52 letters.

Goto tools - options - Custom lists tab

Use the import section to select your 52 letters and Import - Add - OKNow select the cells to be sorted, then Data - Sort, but this time use the Options (at the bottom) and in First Key sort order you will be able to select your new A-Z a-z list. Check the case sensitive check box and OK.

Your list will appear like this:

C E G H O T Y a g h j k p w yOf course you could have made your custom list with lower case first, and you could have included numbers (numbers must be entered as text. Prefix number with ' e.g., '1), if for instance you wanted cells with numbers to be sorted before text, rather than the default after text.

For help on the sort function - in Excel enter 'Sort a Range'

This sort function doesn't require the use of extra cells as data is sorted in-situ.

The 52 A-Z a-z cells you created can be deleted once you have imported them as a custom list.

Regards

Try this: Place your target letters in Cells A1 - D1

In Cell E1 enter the formula:

=CHAR(MIN(CODE(A1),CODE(B1),CODE(C1),CODE(D1)))

Should get you what you are looking for.

MIKE

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? .

Hi, The Sort function puts empty cells at the end of the sorted list and doesn't come up with any errors.

Regards

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.

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 only

There 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 letters

The 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 FunctionRegards

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

tildecharacter because it's ASCII number is 126.I also added the =UPPER() function so even if you enter a lower case

ait will be translated into an upper case.MIKE

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

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

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.

YouMUSTdelete the contents of the cell.

If you simplySpaceover the letter, then the Space Character becomes the lowest ASCII number and that is what will be displayed.MIKE

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)))))

MIKE

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 D1

enter 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+enter

i.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

That was a good way - much easier to replicate

You're welcome

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.

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 set

As before it is an array formula - Ctrl + Shift + EnterRegards

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History