Sort data by numbers first and then text or vice versa

[RAJESH1960]

I am trying to solve this but I am not able to. I have exported this sheet from Tally. In the Debit and Credit columns the amounts are displayed some in numbers and some in format. If I sort the numbers, the formatted numbers should display separately and the normal number should display separately. I tried the answer from one of the older posts, but still I am not able to solve the problem. I need help here.

 

[mamady]

Hi Dear,

to extract numbers only from a cell, please try the following formula (adjust for the reference cell if needed)

=SUMPRODUCT(MID(0&G2, LARGE(INDEX(ISNUMBER(--MID(G2, ROW(INDIRECT("1:"&LEN(G25))), 1)) * ROW(INDIRECT("1:"&LEN(G2))), 0), ROW(INDIRECT("1:"&LEN(G2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(G2)))/10)

Regards

 

[RAJESH1960]

Hi Dear,

to extract numbers only from a cell, please try the following formula (adjust for the reference cell if needed)

=SUMPRODUCT(MID(0&G2, LARGE(INDEX(ISNUMBER(--MID(G2, ROW(INDIRECT("1:"&LEN(G25))), 1)) * ROW(INDIRECT("1:"&LEN(G2))), 0), ROW(INDIRECT("1:"&LEN(G2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(G2)))/10)

Regards

It ain't working. I need the characters and numbers as they are. I don't want to convert characters. I want to sort in such a way that the numbers are separated first and then the numbers with characters.

 

[RAJESH1960]

It ain't working. I need the characters and numbers as they are. I don't want to convert characters. I want to sort in such a way that the numbers are separated first and then the numbers with characters.

I tried flash fill function in extra columns and it got solved. Thanks for replying Mamady.