Forum Discussion
Sorting column with Concat formula does not produce Low to High
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.
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?