Sorting Text within a cell

**Yellow Dinosaur** · 07-27-2021, 12:43 AM

Hi all,

Does the function to sort text within a cell exist or a work around. Currently my idea is to break them into multiple cells, transpose, sort transpose again, and recombine. I think this is possible but will take much time and resources as I am currently working with at least 30 thousand line. Would appreciate any help on this... even a VBA solution would be great if that is the only possible way!

Example cell: (within a cell) Orange Apple Banana Pineapple Grape
Desired result: Apple Banana Grape Orange Pineapple

background of problem: I need to compare data between two worksheet. However, no common data between the two data set except name is available. The issue is the name format is different (last, middle, first, etc) and inconsistent (some has . and between 2 to 5 names). My solution is to sort the name column in both worksheet to get a common denominator to work with.

Thanks in advance! Hope every one stays safe in this pandemic times. Cheers

**AliGW** · 07-27-2021, 01:32 AM

Welcome to the forum.

Your Excel version is MS365 (days in a year) not 360 (degrees in a circle) - please update your profile.

I think you will need VBA.Have a look here: https://www.excelforum.com/excel-gen...ular-cell.html

**wk9128** · 07-27-2021, 01:32 AM

Office 365
Suppose your within a cell is in cell A1
cell B1 formula

HTML Code:

**AliGW** · 07-27-2021, 01:34 AM

Oooh, nice one!

**AliGW** · 07-27-2021, 02:12 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**Yellow Dinosaur** · 07-27-2021, 02:25 AM

Thank you for pointing that out!

i have looked at the linked prior to posting. Unfortunately it would not work with my data set. As the text would need to be separated by a comma between each word. I am trying to understand the VBA code that determines the comma as the separator but no luck so far. Thank you!

**AliGW** · 07-27-2021, 02:28 AM

Did you see post #3?

You did not tell us in your opening post that you wanted to separate the text with a comma - how did you expect us to guess that???

Try changing this:

TEXTJOIN(" ",TRUE,SORT(FILTERXML("<A>"&SUBSTITUTE(A1," ","")&"</A>","//B")))

to this:

TEXTJOIN(",",TRUE,SORT(FILTERXML("<A>"&SUBSTITUTE(A1," ","")&"</A>","//B")))

**Yellow Dinosaur** · 07-27-2021, 02:30 AM

@wk9128

Thank you for your reply! i appreciate it very much. however I can get it to work. it says "sort" is not a valid function

**AliGW** · 07-27-2021, 02:32 AM

In that case, you do NOT have MS365!!!

https://support.microsoft.com/en-us/...d-c20e8e44b86c

So which version do you have???

**Yellow Dinosaur** · 07-27-2021, 02:33 AM

@AliGW

sorry for the confusion. I do not want a comma. I meant the solution to the link you shared would not work as the problem in that link uses a comma as a separator. Since my data set does not, it would require a different approach or some modification to the VBA code.

**AliGW** · 07-27-2021, 02:42 AM

Please see and respond to post #9.

You are causing confusion on an industrial scale and in the process wasting our time.

**Yellow Dinosaur** · 07-27-2021, 02:46 AM

im sorry for wasting your time. Im running it on a company laptop. Under account it says subscription product : microsoft office 365 ProPlus. So i dont know what other version it could be. Im just trying to get some help on a subject i have limited knowledge on. Would appreciate if you could be more patient

**AliGW** · 07-27-2021, 02:50 AM

Right - it looks like the version of 365 you are running is not up-to-date, then.

Would appreciate if you could be more patient

I will leave this to someone else to answer. Good luck!

**JohnTopley** · 07-27-2021, 02:55 AM

It would (will!) help if you can post a sample workbook.

See the yellow banner at top of the page on how to attach a workbook.

**tanasedn** · 07-27-2021, 03:45 AM

I found this thread in this forum.
https://www.excelforum.com/excel-gen...ular-cell.html
Maybe this is what you need.

**AliGW** · 07-27-2021, 03:47 AM

Diana - I already posted that link in post #2. The OP has rejected it.

**tanasedn** · 07-27-2021, 03:59 AM

Ali, you're right. It got out of my sight

**AliGW** · 07-27-2021, 04:00 AM

No worries.

Sorting Text within a cell

LinkBack

Thread Tools

Rate This Thread

Display

Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Re: Sorting Text within a cell

Thread Information

Users Browsing this Thread

Similar Threads

Sorting cell content based on text within text?

[SOLVED] Sorting through text in single cell

sorting text from one cell into 3 other cells based on text criteria

need help sorting by partial text in a cell - I think! :)

[SOLVED] Sorting cell that contains TEXT only

Excel 2007 : Sorting text within a cell

Sorting text within a cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions