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

%3CLINGO-SUB%20id%3D%22lingo-sub-2091510%22%20slang%3D%22en-US%22%3EConvert%20Ordinal%20to%20Cardinal%20number%20with%20the%20help%20of%20Formula%20or%20VBA%20code.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091510%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3Asmiling_face_with_smiling_eyes%3A%3C%2FP%3E%3CP%3EHow%20to%20Convert%20Ordinal%20number%20to%20Cardinal%20Number%20with%20the%20help%20of%20Formula%20or%20VBA%3F%3C%2FP%3E%3CP%3ELike%20-%26nbsp%3B%3C%2FP%3E%3CP%3E1st%20--%201%3C%2FP%3E%3CP%3E2nd%20--%202%3C%2FP%3E%3CP%3E3rd%20--%203%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3C%2FP%3E%3CP%3EWaiting%20for%20your%20reply...%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2091510%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2091566%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Ordinal%20to%20Cardinal%20number%20with%20the%20help%20of%20Formula%20or%20VBA%20code.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3BPerhaps%20like%20this%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-01-24%20at%2014.45.08.png%22%20style%3D%22width%3A%20361px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249006i081DF1209710C3CD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-01-24%20at%2014.45.08.png%22%20alt%3D%22Screenshot%202021-01-24%20at%2014.45.08.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2091570%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Ordinal%20to%20Cardinal%20number%20with%20the%20help%20of%20Formula%20or%20VBA%20code.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091570%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20sir...%20You%20are%20correct%3Asmiling_face_with_smiling_eyes%3A%3C%2FP%3E%3CP%3ESir%20what%20if%20i%20want%20to%20change%20to%20-%3C%2FP%3E%3CP%3E22nd%20Dec%20-%2022-Dec-2021(in%20date%20format)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2091606%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Ordinal%20to%20Cardinal%20number%20with%20the%20help%20of%20Formula%20or%20VBA%20code.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091606%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3BNot%20pretty%2C%20but%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B13%2C%22st%20%22%2C%22-%22)%2C%22nd%20%22%2C%22-%22)%2C%22rd%20%22%2C%22-%22)%2C%22th%20%22%2C%22-%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-01-24%20at%2015.25.31.png%22%20style%3D%22width%3A%20777px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249007iA31900A7A8FADF50%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-01-24%20at%2015.25.31.png%22%20alt%3D%22Screenshot%202021-01-24%20at%2015.25.31.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Hello

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

@Riny_van_Eekelen 

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