+ Reply to Thread
Results 1 to 10 of 10

Extracting the longest value out of space separated text values

  1. #1
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2016 Professional Plus
    Posts
    128

    Extracting the longest value out of space separated text values

    Hi Everyone!
    I have attached an excel sheet where column A contains space sperated text values(Which are english names). Now I want the longest text value out of each cell into column B for each corresponding cell in column A.

    If we rephrase the above requirement, we are just extracting the longest text string out of the each row in column A. So cell A1 CONTAINS value "Sun Moon Sky" then the corresponding value in column B1 should be "Moon" as it have 4 characters which is the highest among all 3 words.

    Please check the attached file. Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Extracting the longest value out of space separated text values

    So what about your first example?

    Jeana Dames Jack Young = which one???

    You need to add to the sample workbook 10-15 rows of results data entered manually.

    Are you still using Excel 2007?
    Last edited by AliGW; 08-03-2020 at 11:34 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Extracting the longest value out of space separated text values

    You also need to remove a lot of non-printing characters from your imported values: some of those 'spaces' are not spaces at all. Once you've done that, you could use:

    =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),50*(MATCH(1,0/FREQUENCY(0,1/(1+LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),50*{0,1,2,3,4,5}+1,50))))))-1)+1,50))

    and copied down.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Extracting the longest value out of space separated text values

    another alternative would to be use a basic UDF

    Please Login or Register  to view this content.
    the above, stored in a standard module, could be called from the cell with:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2016 Professional Plus
    Posts
    128

    Re: Extracting the longest value out of space separated text values

    Then it could be any one if it has multiple qualified options. Lets say first one

  6. #6
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2016 Professional Plus
    Posts
    128

    Re: Extracting the longest value out of space separated text values

    Quote Originally Posted by XOR LX View Post
    You also need to remove a lot of non-printing characters from your imported values: some of those 'spaces' are not spaces at all. Once you've done that, you could use:

    =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),50*(MATCH(1,0/FREQUENCY(0,1/(1+LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),50*{0,1,2,3,4,5}+1,50))))))-1)+1,50))

    and copied down.

    Regards
    How can we remove that non printing characters. I have list of 5000 entries. Can we do it automatically?

  7. #7
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Extracting the longest value out of space separated text values

    You can remove those characters like this:

    1) Copy the character between "Jeana" and "Dames" in cell A1. To do this, select the characters which looks like a "space" character and copy it by Ctrl+C

    2) Select column A and execute Find/Replace method.

    3) In the "Find What" box, paste the copied character by Ctrl+V then, in the "Replace With" box enter a single space and hit the button "Replace All"

    As an alternative, if you wish to do this with a formula, you can enter this in cell B1 and copy downwards...

    Please Login or Register  to view this content.
    Note: Change ";" with "," according to your regional settings.

    As a last point; you can use this formula in XOR LX 's suggested formula as:

    Please Login or Register  to view this content.
    Last edited by Haluk; 08-03-2020 at 01:47 PM. Reason: typo...

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Extracting the longest value out of space separated text values

    Or, in a single formula:

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," ")," ",REPT(" ",50)),50*(MATCH(1,0/FREQUENCY(0,1/(1+LEN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," ")," ",REPT(" ",50)),50*{0,1,2,3,4,5}+1,50))))))-1)+1,50))

    Regards

  9. #9
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2016 Professional Plus
    Posts
    128

    Re: Extracting the longest value out of space separated text values

    Quote Originally Posted by XOR LX View Post
    Or, in a single formula:

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," ")," ",REPT(" ",50)),50*(MATCH(1,0/FREQUENCY(0,1/(1+LEN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," ")," ",REPT(" ",50)),50*{0,1,2,3,4,5}+1,50))))))-1)+1,50))

    Regards
    Yes this formula works fine in first go. Thanks. Can we tweak this formula to find the shortest word which is opposite to what was my initial requirement?

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Extracting the longest value out of space separated text values

    Sure.

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," ")," ",REPT(" ",50)),50*(MATCH(1,0/FREQUENCY(1,LEN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," ")," ",REPT(" ",50)),50*{0,1,2,3,4,5}+1,50)))))-1)+1,50))

    Regards

+ 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] splitting a cell that has two values separated by a space
    By Nerpilis in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 06-13-2013, 10:23 PM
  2. Seperating values that are space-separated in one cell to several cells
    By ferferi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-24-2013, 05:58 AM
  3. [SOLVED] Pasting space separated values
    By quarinteen1 in forum Excel General
    Replies: 4
    Last Post: 05-09-2013, 11:32 PM
  4. [SOLVED] Extracting Text Separated by Commas
    By davidmg_13 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2013, 05:18 PM
  5. Replies: 7
    Last Post: 02-10-2013, 02:10 AM
  6. Parse Strings of Space-Separated Text of Varying Lengths
    By mlexcelhelpforum in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-09-2011, 07:49 AM
  7. Parsing Text Separated by A Space
    By scotfitz in forum Excel General
    Replies: 1
    Last Post: 11-13-2008, 02:19 PM

Tags for this Thread

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