+ Reply to Thread
Results 1 to 13 of 13

rearranging order of text string inside a cell

  1. #1
    Registered User
    Join Date
    04-09-2009
    Location
    NJ, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    rearranging order of text string inside a cell

    I need help rearragning text on excel. I believe Macro will be needed to solve this problem so if anyone can help me out, it will be a big help. Thank you.

    Example 1:
    I need to rearrange
    "trace Silt, cm SAND, some- f Gravel" into
    "cm SAND, some- f Gravel, trace Silt"

    Example 2:
    I need to rearrange
    "some+ Silt, f SAND, trace- f Gravel" into
    "f SAND, some+ Silt, trace- f Gravel"

    There are many different cases for this problem. So if there is a way for the computer to recognize the Capitalized word and move it to the front and rest should be arange in order listed below.

    Order
    and+
    and
    and-
    some+
    some
    some-
    little+
    little
    little-
    trace+
    trace
    trace-

    each term is seprarated by commas.
    For example
    f SAND, some+ Silt, trace- f Gravel
    each color represent one complete sentence. and the order for them should be capitalized funciton first, then the follow the order provided above.
    Last edited by Milan Limbachia; 04-09-2009 at 04:42 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: rearranging order of text string inside a cell

    Is there a maximum number of comma-delimited substrings in the whole thing?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-09-2009
    Location
    NJ, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: rearranging order of text string inside a cell

    Yes. It will not be more than 3.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: rearranging order of text string inside a cell

    I came up with this... not very elegant mind:

    Please Login or Register  to view this content.
    Best to place in a stand alone Module

    Would be called from a cell along the lines of:

    =REORDER($A3,$F$1:$F$12,",")

    Where:

    A3 = string to be re-ordered
    F1:F12 = range containing your list of priorities (in order in which they should be returned - ie as you have listed)
    "," represents the delimiter within the string itself (ie that separates the phrases in A3)

    No doubt this will be improved upon but it worked for me based on your sample strings...

  5. #5
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: rearranging order of text string inside a cell

    Hi

    I tested this UDF with the examples you posted. It accepts more than 3 comma-delimited substrings and duplicate keywords.

    If you find errors please post examples.

    Use:

    =Rearrange(A1)


    Please Login or Register  to view this content.
    Last edited by lecxe; 04-09-2009 at 11:02 PM.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: rearranging order of text string inside a cell

    Same principle as mine but undoubtedly a 1000+ times more elegant - brilliant stuff lecxe !

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: rearranging order of text string inside a cell

    That is VERY nice, lexce.

    Would you explain this part of the regex pattern:
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-09-2009
    Location
    NJ, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Thumbs up Re: rearranging order of text string inside a cell

    Thank you very much lecxe and donkeyote for your help. I tried lecxe's code and works fine.

    Once again, Thank you very much for your quick response.

    ---
    Milan Limbachia

  9. #9
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: rearranging order of text string inside a cell

    Quote Originally Posted by shg View Post
    That is VERY nice, lexce.

    Would you explain this part of the regex pattern:
    Please Login or Register  to view this content.
    It's a negative lookahead (the (?!\w)) which means it matches upper case character strings of at least 2 characters ([A-Z[{2,}) which isn't followed by a word character (so it makes sure the character string is only uppercase). It's a clever construct.

    Richard
    Richard Schollar
    Microsoft MVP - Excel

  10. #10
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: rearranging order of text string inside a cell

    Hi

    Thank you and you're welcome.

    Richard explained the negative lookahead.

    I must say, however, that it shouldn't be there. I needed it for a previous version of the solution but then I made sure that every keyword is preceeded and followed by a space (with the Replace() at the beginning). I then changed every element of the array and missed the first one.

    It should be:

    " [A-Z]{2,} "

    This way it's simpler, better, and consistent with the other elements of the array.

    Cheers

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: rearranging order of text string inside a cell

    Thanks, Richard, but still confused about (?!\w)"

    What does ? refer to -- zero or more what?

    The parens specify a pattern to be remembered, but that's not used.

    The ! is not a quantifier, so what is it?

    And the non-word character is \W, not \w



    EDIT: Thanks, lexce, I though it was unneeded, but still don't understand what it would do ...

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: rearranging order of text string inside a cell

    EDIT: Never mind, lexce, I'm just sitting here reading Wyatt's book about negative look-aheads.

    Richard, lexce, thank you.

  13. #13
    Registered User
    Join Date
    12-10-2012
    Location
    Boulder, CO
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: rearranging order of text string inside a cell

    This rearrange function is just wonderful!... but I am having trouble getting it to do what I need in my context. Mainly because I lack both the regex and the VBA necessary.

    Questions (help with any one is deeply appreciated):
    1. The rearrange seems to work on “sort the words in this order if the match is exact”. I would like it to have the sort be based on “sort the words when the words begin with”.
    2. This function returns nothing when your sort words have special characters in them. My context is that I have lines that have macro variables in them where the macro variables vary in how they are delimited.
    As examples… I have the lines “~s=male~,!a=11!, @r=Hispanic@,#y=40000#” AND “~s=female~,!a=10!, @r=Hispanic@,#y=30000#”

    On the first line: *** is male, age is 11, race is Hispanic, income is 40000. I would like to rearrange this so the Y’s always come first, S’s come second, R’s come third, and A’s come last… regardless of what their value is and regardless of what their delimiter is. Is this possible? Can one quote the special characters somehow?
    3. Can the sort words be passed to the function as a range (i.e., to the array assignment line) like in the earlier variation by DonkeyOte?


    Thank you so much!
    Brendan




    lexce wrote:
    >>>>


    Quote Originally Posted by lecxe View Post
    Hi

    I tested this UDF with the examples you posted. It accepts more than 3 comma-delimited substrings and duplicate keywords.

    If you find errors please post examples.

    Use:

    =Rearrange(A1)


    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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