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

Report •


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

MIKE

http://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?

MIKE

http://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
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	y

To 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 - OK

Now 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
y

Of 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


Report •

#10
September 26, 2009 at 08:13:01
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

http://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 in

This 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 function

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

MIKE

http://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.

MIKE

http://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)))))

MIKE

http://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 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


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 set
As before it is an array formula - Ctrl + Shift + Enter

Regards


Report •


Ask Question