sort characters within a cell

[tehs0001]

Hi all, I need help here - I have a column of characters in each cell.

e.g.

A1
qwerty
asdfgh
zxcvbn

What I need is to sort the characters within the cell alphabetically, i.e. the result should be:

A1
eqrtwy
adfghs
bcnvxz

any way to do this? much appreciated, thank you!

[quekbc]

Are they always the same length?

[tehs0001]

no, differing lengths

[quekbc]

Doing it via VBA would be your best option. Doing it with formulas is quite tedious. Would that suit you?

Also, should capital letters be sorted accordingly also?

I.e. should aBCdEf be
1) aBCdEf; or
2) BCEadf; or
3) adfBCE

[tehs0001]

thanks for your reply!

captial letters should be sorted accordingly, so it should be 1) aBCdEf

[quekbc]

Write the following code into a module within VBA.

Then in Excel, say A1 has qwerty, type in any other cell =SortLetters(A1)

Edit: See below.

[MarvinP]

I get an error in your code with lLength.

It looks like you try to redim it before it has a value. Care to test your code before posting it for us guys who try to use it?

[quekbc]

My bad. I did test it. Then I pasted it over. Not sure what happened there. I must've accidentally moved it.

Please Login or Register  to view this content.

As a side note, whilst I appreciate any criticism that you may have, I do not appreciate the implication that I did not test my solutions before pasting it here. Sure that may happen for a simple solution but my brain is far from being able to write the above directly here and have it working at first go.

[MarvinP]

I was very excited and interested in how you did this problem. Then I pasted your code in a module and was disappointed it gave an error, instead of working. Sorry if I was a little short as I was really wanting it to work.

[quekbc]

Not a problem, Marvin. It should be working now.

[tehs0001]

thank you so much quekbc for your help. it works fine for me.

[quekbc]

Excellent. That's good to hear. If this resolves your question, please mark the thread as Solved by clicking Thread Tools above your first post, and select "Mark your thread as Solved".