# combine mid and max

1. ## combine mid and max

example.xlsxColumn A (ticket numbers)

DAS-011 E
DAS-012 E
DAS-013 E

I want to look up the maxium ticket number in column A. so all i need is the formula to return the value "013". Can this be done?

I tried creating column b using the MID formula =MID(A3,7,3) and then just doing a MAX formula but all it returns is a zero.

2. ## Re: combine mid and max

Try this array formula in B1

=INDEX(A1:A3,MATCH("*"&MAX(--MID(A1:A3,FIND("-",A1:A3)+1,3))&"*",A1:A3,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

 A B 1 DAS-011 E DAS-013 E 2 DAS-012 E 3 DAS-013 E

3. ## Re: combine mid and max

One way as an array formula

Formula:
`Please Login or Register  to view this content.`

However a smarter option might be to use Data TextToColumns to split the number into its own column and then just use a standard MAX(A1:A3)

4. ## Re: combine mid and max

ok that works. Is there anyway i can have that formula return what the next consecutive number would be? so the result would be "DAS-014 E"

5. ## Re: combine mid and max

Originally Posted by AlKey
Try this array formula in B1

=INDEX(A1:A3,MATCH("*"&MAX(--MID(A1:A3,FIND("-",A1:A3)+1,3))&"*",A1:A3,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

 A B 1 DAS-011 E DAS-013 E 2 DAS-012 E 3 DAS-013 E
ok that works. Is there anyway i can have that formula return what the next consecutive number would be? so the result would be "DAS-014 E"

6. ## Re: combine mid and max

="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1)),"000")&" E"

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

7. ## Re: combine mid and max

Originally Posted by AlKey
="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1)),"000")&" E"

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
that works perfect as well. now if i could make it more complicated I have something else i would like to add.

Column A

DAS-011 E
REQ-019 E
DAS-013 E
DAS-012 E
REG-020 E

Now i want to lookup just the DAS in column A and have it return the next consecutive number. so the result would be DAS-014 E

8. ## Re: combine mid and max

Hi,

same formula

="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1))+1,"000")&" E"

Hope it helps

9. ## Re: combine mid and max

Originally Posted by canapone
Hi,

same formula

="DAS-"&TEXT(MAX(VALUE(MID(A1:A3,5,3)+1))+1,"000")&" E"

Hope it helps
I am terrible at explaining things. can you please look at my example and let me know if this is possible.

Thanks

example.xlsx

10. ## Re: combine mid and max

Hi,

confirmed with control+shift+enter

=VLOOKUP(I7,A6:B9,2,0)&"-"&TEXT(MAX(VALUE(RIGHT(D6:D17,3)+1)),"000")

could do the trick.

Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1