How can I use a formula to sort text values?

[stuckagain22]

I have a sheet that I want to auto sort the data in one column into another column.

In Example:

If column A contains:

peach
apple
orange

I want column B to then show:

apple
orange
peach

Is there anyway to do this using a function/formula?

 

[mikerickson]

In C1 (fill down)
=IF(ISBLANK(A1),300,CODE(A1))

In column B
=INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0),1)

 

[stuckagain22]

Thanks, but this only indexes the value of the first text character. If there are several values that start with the same character, then they would not be alphabetical ie:

peach
pear
pomegranate

 

[Aladin Akyurek]

Thanks, but this only indexes the value of the first text character. If there are several values that start with the same character, then they would not be alphabetical ie:

peach
pear
pomegranate

If you have already installed the free morefunc.xll add-in...

Book5
	A	B	C	D
1			5
2	peach		apple
3	pear		cranberry
4	pomegranate		peach
5	apple		pear
6	cranberry		pomegranate
7
Sheet1

C1:

=COUNTA(A2:A6)

C2, copied down:

=IF(ROWS($C$2:C2)<=$C$1,INDEX(VSORT($A$2:$A$6,$A$2:$A$6,1),ROWS($C$2:C2)),"")

 

[Richard Schollar]

See this link:

http://www.dailydoseofexcel.com/archives/2006/10/11/dynamic-text-sorting/