| Home > Forum Home > Automating Data Analysis Tasks > Finding max value based on data that contains both text and numbers | Share |
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
| Forum Topic | Post Reply Login |
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 | ||
| Replies - Displaying 1 to 3 of 3 | Order Replies By: Most Recent | Chronological | Highest Rated |
 | Rate this: (3/5 from 1 vote) 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 |
| Rate this: (3/5 from 1 vote) 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 |
 | Rate this: (3/5 from 1 vote) |
| Excel Business Forums Administrator | |
| Posted by Excel Helper on |
| Displaying page 1 of 1 |
| Find relevant Excel templates and add-ins for Finding max value based on data that contains both text and numbers in the Excel Business Solutions Directory |