## 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

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

### Leave a Reply

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

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

your 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.