rearranging order of text string inside a cell

[Milan Limbachia]

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.

[shg]

Is there a maximum number of comma-delimited substrings in the whole thing?

[Milan Limbachia]

Yes. It will not be more than 3.

[DonkeyOte]

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...

[lecxe]

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.

[DonkeyOte]

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

[shg]

That is VERY nice, lexce.

Would you explain this part of the regex pattern:

Please Login or Register  to view this content.

[Milan Limbachia]

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

[Richard Schollar]

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

[lecxe]

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

[shg]

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 ...

[shg]

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

Richard, lexce, thank you.

[whatappears]

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:
>>>>

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.