+ Reply to Thread
Results 1 to 7 of 7

Max in column with text

  1. #1
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    762

    Max in column with text

    Good evening,
    I ask for your help to find the maximum value in column B containing some text
    In the attached file in A1 I manually entered the desired result
    the maximum value of column B.
    Thank you
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,532

    Re: Max in column with text

    Is the max value only the last four digits of the text. Is the text always preceded by 12/? What would happen if there was different precedent?

  3. #3
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    762

    Re: Max in column with text

    Hi the final digits are 4 example 0001,0125...
    is preceded by 2 digits which can be 12,15,18,20 ....

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,532

    Re: Max in column with text

    Yes, but do the preceding digits, ie. 12 ,15 etc. have any thing to do with determining the max number.

  5. #5
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    762

    Re: Max in column with text

    No, consider that the previous figures can indicate the last 2 digits of a year then we have the / and then that progressive 4-digit number but, not in order on the lines, the largest is not said to be in the last line yes can find in any cell of column B

  6. #6
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,263

    Re: Max in column with text

    Please try
    =TEXT(MAX(INDEX(--TEXT(SUBSTITUTE(RIGHT(B2:B39,4),"/",),"0;;;\0"),)),"AA 00\/0000")

    or only consider last 4 digit

    =VLOOKUP(TEXT(MAX(INDEX(--TEXT(RIGHT(B2:B39,4),"0;;;\0"),)),"\*0000"),B2:B39,1,0)
    Attached Files Attached Files
    Last edited by Bo_Ry; 06-25-2020 at 01:30 PM.

  7. #7
    Forum Contributor
    Join Date
    05-16-2012
    Location
    Latina, ITALY
    MS-Off Ver
    Excel 2007--2010
    Posts
    762

    Re: Max in column with text

    Hi Bo,
    great that you solve my problems
    Thank you, it works
    goodbye see you soon

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] duplicate text in empty cells along the column until new text appears in that column
    By aaaaa34 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-23-2019, 07:01 AM
  2. VBA: Look for partial text in Column A from Column B and paste text offset Column A
    By coryspeth in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-07-2018, 05:53 AM
  3. [SOLVED] Macro to Copy and Paste Specific Text in Column A on the last line with Text in Column B?
    By D2S in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2017, 04:08 PM
  4. [SOLVED] VBA Macro to find text string in one column and replace specific text in another column if
    By bmahfood in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-21-2017, 11:15 AM
  5. Replies: 3
    Last Post: 04-21-2016, 02:52 AM
  6. Filter column by text, then export column to new workbook, replace searched text with new.
    By Headhunter234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2015, 07:48 AM

Bookmarks

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