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

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.