## Sort and return unique single digit numbers

**Eddie asks:**

*Find and Sort*

* I have question, in range A1,A2,A3,A4 contain 097 494 861 575.*

*What is the formula in excel if the result that i want is 01456789*

I use two formulas, one array formula in cell range D6:K6 and a regular formula in cell C5.

**Formula in cell C5:**

This formula concatenates values in cell range D6:Q6.

**Array formula in cell D6:**

Enter array formula in cell D6 and press and hold CTRL + SHIFT simultaneously and then press Enter. Check your formula in the formula bar, it should begin and end with a curly bracket. Like this:

Copy cell D6 and paste to E6:Q6.

### Explaining the array formula

- The formula splits all characters into single digit numbers.
- Checks that only unique values are returned.
- Sorts the array from 0 to 9 and returns values in a cell each.

### Download excel *.xlsx file

### Functions in this post

**IF(**logical_test, [value_if_true], [value_if_false])

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**COUNTIF(**range,criteria**)**Counts the number of cells within a range that meet the given condition

**SMALL(**array,k**)**

Returns the k-th smallest number in this data set.

**IFERROR**(value, value_if_error)

If the *value* argument returns an error, the *value_if_error *argument is used. If the *value* argument does NOT return an error, the IFERROR function returns the v*alue *argument.

**MID(**text, start_num, num_chars**)
**Returns the characters from the middle of a text string, given starting position and length

### Category: Sorted unique distinct values

Create a unique distinct alphabetically sorted list, extracted from a column

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]Comments(53) Filed in category: Excel, Sorted unique distinct values

Create a unique distinct sorted list containing both numbers text removing blanks

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]Comments(24) Filed in category: Excel, Sorted unique distinct values

Extract a unique distinct list sorted alphabetically removing blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]Comments(21) Filed in category: Excel, Sorted unique distinct values

Create a unique distinct list and sort by occurrances from large to small

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]Comments(15) Filed in category: Excel, Sorted unique distinct values

Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed

Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: […]Comments(11) Filed in category: Excel, Sorted unique distinct values

Filter unique distinct list sorted based on sum of adjacent values using array formula in excel

Table of Contents Filtering unique distinct text values and sort them based on sum of adjacent values Filtering unique distinct […]Comments(10) Filed in category: Excel, Sorted unique distinct values

Unique distinct list from a column sorted A to Z using array formula

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Answer: […]Comments(6) Filed in category: Excel, Sorted unique distinct values

Unique distinct records sorted by frequency

Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on […]Comments(3) Filed in category: Excel, Sorted unique distinct values

Extract a unique distinct list sorted from A-Z from range in excel

Inspired from a comment in this article Unique values from multiple columns using array formulas I have now created this […]Comments(3) Filed in category: Excel, Sorted unique distinct values, Unique distinct values

### 6 Responses to “Sort and return unique single digit numbers”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

You could always use the brute force method (which eliminates the need for any helper cells)...

=IF(COUNTIF(A1:A4,"*0*"), 0, "")&IF(COUNTIF(A1:A4, "*1*"), 1, "")&IF(COUNTIF(A1:A4, "*2*"), 2, "")&IF(COUNTIF(A1:A4, "*3*"), 3, "")&IF(COUNTIF(A1:A4, "*4*"), 4, "")&IF(COUNTIF(A1:A4, "*5*"), 5, "")&IF(COUNTIF(A1:A4,"*6*"), 6, "")&IF(COUNTIF(A1:A4, "*7*"), 7, "")&IF(COUNTIF(A1:A4, "*8*"), 8, "")&IF(COUNTIF(A1:A4, "*9*"), 9, "")

And for the VBA code fans out there, one could use this UDF (user defined function)...

Rick Rothstein (MVP - Excel),

Thank you for commenting.

Rick,

For me it's not working your formula (and UDF).

@Matt,

Just a guess, but your cells need to be formatted as Text before you enter the numbers into the cells or, alternately, enter your numbers into the cells preceded by an apostrophe (in order to make Excel see them as Text).

Can you bring unique within a single cell

mahmoud-lee,

See Rick Rothstein's formula.