Sort by number and ignore characters.

[Ataraxicatom]

Ok, here is what I am trying to do:
132.45
132.46
J132.45

The J132.45 should come before 132.46
I have built macros to pull information from one sheet to multiple sheets, but I can't get it to sort properly because some of the values have letters in front. Any way to ignore the letters?

Thanks!

[MarvinP]

Hi Ataraxicatom,

The Value() function might be what you need in a Helper Column and then sort by the numbers.

I've used a formula of:
=IFERROR(VALUE(A1),IFERROR(VALUE(MID(A1,2,40)),VALUE(MID(A1,3,40))))
for an example in the attached.

If there is lots of long text in front of the numbers, perhaps a VBA User Defined Function (UDF) would be more appropriate.

[Ataraxicatom]

Thanks! The helper column was exactly what I needed.