Convert Ordinal to Cardinal number with the help of Formula or VBA code.

Regular Contributor


How to Convert Ordinal number to Cardinal Number with the help of Formula or VBA?

Like - 

1st -- 1

2nd -- 2

3rd -- 3 

and so on.


Please help..??

Waiting for your reply...???


5 Replies


Yes sir... You are correct

Sir what if i want to change to -

22nd Dec - 22-Dec-2021(in date format)

@Zan_Hanifee Not pretty, but like this:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B13,"st ","-"),"nd ","-"),"rd ","-"),"th ","-"))

Screenshot 2021-01-24 at 15.25.31.png