Excel formulas to extract information

Conernesto
Conernesto used Ask the Experts™
on
I need two excel formulas. I need a formula for column B that will give me what I have in column A without the last two digits. The next formula in column C will give me the last to digits from column A. Can you provide this formulas?  

Column A                                          Column B                                    Column C

Have                                               Want 1                                            Want 2
                        
Ukraine UP                                       Ukraine                                    UP
United Arab Emirates AE               United Arab Emirates            AE
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
RayData Analyst

Commented:
use these formulas
in column b1...                  =left(a1, len(a1)-2)
ijn column c1...                  = right(a1, 2)

Author

Commented:
I am not getting the right result using the right formula. The left formula works fine.

Author

Commented:
I changed the right formula from = right(a1, 2)  to = right(a1, 3)  and I am now getting the correct result. Thank you.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
That means you have some trailing spaces in the end. To deal with them you may try the following formulas...

=LEFT(TRIM(A1),LEN(TRIM(A1))-2)

Open in new window

=RIGHT(TRIM(A1),2)

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial