Finding max value based on data that contains both text and numbers

Excel Help for Finding Max Value Based On Data That Contains Both Text And Numbers in Automating Data Analysis Tasks

Finding Max Value Based On Data That Contains Both Text And Numbers

Rate this: (3/5 from 1 vote)

	I need a formula that gives me a max value from a range that contains both text and numbers.Let's say I have a column which has the following data (these are names of data files): CAR-IA-11 CAR-IA-12 CAR-IO-13 NCR-C-125 NCR-S-126 NCR-I-127 Then I want a formula that tells me what was the latest CAR file entered in the list. So the formula would find the highest number associated with the text "CAR" in this column. Similarly I want a formula that gives me the latest NCR file entered into the column. I would highly appreciate your assistance on this one.
	Maggy
	Posted by Margretjona on 18 Apr 2011

Replies - Displaying 1 to 3 of 3

Order Replies By: Most Recent | Chronological | Highest Rated

	Split values to use MAX function Rate this: (3/5 from 1 vote) The easiest solution would be to split the values by the "-" character so that the MAX function can be used on the part required. To do this, you can use the Text to Columns functionality and choose the "-" as the separator.  Otherwise FIND, LEFT, RIGHT and IF functions can be used in combination within a somewhat complex formula.
	Excel Business Forums Administrator
	Posted by Excel Helper on 18 Apr 2011

	Values split, having troubles with creating a correct formula. Rate this: (3/5 from 1 vote) Thanks for your reply.  I have split the values by the "-" into 3 columns. But I am still having problems with creating a formula that returns a max value based on whether a file is of type "CAR" or "NCR". How would I utilize the 3 columns to get these two different max values (one for CAR and one for NCR files). Could I use the DMAX formula? Thanks, Margret
	Maggy
	Posted by Margretjona on 19 Apr 2011

	MAX with IF formula Rate this: (3/5 from 1 vote) The DMAX will not work with multiple column criteria. The best option is to use a combination of MAX and IF in an array formula. A detailed example of this can be found here.
	Excel Business Forums Administrator
	Posted by Excel Helper on 19 Apr 2011

Displaying page 1 of 1