Sorting column with Concat formula does not produce Low to High

[LansFP]

I am using the Concat formula to concatenate numbers in columns A and B into C.  however when I sort the column C, the numbers are not sorted Lowest to Highest.  For example the sort goes like this:

1

1.08

10

10.98

100

109

11

I have formatted the cells as Currency (yet no $ appears). I have used the Value function in combination with Concat, to make sure the numbers were not entered as Text.

Please help.

[#2]

@LansFP 

I am using the Concat formula to concatenate numbers in columns A and B into C. 

CONCAT does not work with numbers as values; it works with text.  So by definition, your "numbers" are text during the time you're using CONCAT.

Which makes we want to know what was in columns A and B in order to end with the figures you show as being in column C.

That sorting order you show is exactly what would happen with text when sorted:

Wouldn't matter how many zeroes followed the "1", 11 would come after 1000000. 

So could you give us a more complete picture here (ideally, not a picture per se, but the actual spreadsheet, posted on OneDrive or GoogleDrive, with a link here that grants access to it)...... 

• What's in columns A and B
• what formulae are you using to concatenate and to incorporate the VALUE function?

[#3]

@LansFP 

It sounds like A and B have numbers but it's not certain which column might have the number in a given row.

If that's the case, you could use something like this:

=SORT(TOCOL(A1:B20,1))

[#4]

Thanks @Patrick2788 .

the spreadsheet looks like this:

Sorting the Concate column (D) does not sort in either ascending or descending order, but rather sorts in, what I think is called, a decimal sort.

Thank you for your help.

LansFP.

[#5 · Accepted solution]

@LansFP 

Using CONCAT on numbers converts the results to text. I just did it to test. You're not showing us here where or how you used VALUE, as you said you did in your opening post.

So the sort of your column D is going to sort as text unless you've done something else in an intermediate stage.

An easier way to get the value in A or the value in B, which really appears to be your goal, since one or the other of the two columns is going to be blank, given the "Debit"/"Credit" headings, would be the formula

=IF(A3="",B3,A3)  or =MAX(A3,B3); the latter works since both numbers are positive and one of them, by definition, is zero.

CONCAT should be reserved for its intended purpose, which is concatenating--putting together--two text strings.

[#6]

Excellent. I'll remember concat converts.
You are very good at reading minds.
Thanks!!

[#7]

@LansFP 

You're very welcome.

You are very good at reading minds.

I have a wife who would take issue with you on that!  [Smiley face]