Excel formula for how to split a multidigit number in 1 cell into 2 cells?

Question

Hi all. I need to be able to split a number into 2 cells. The right cell would have the 5 right digits, and the left 4 or 5 digits would be in the left cell as shown:

Original	First 4 or 5 digits	Last 5 digits
900312345	9003	12345
0900312345	9003	12345
9000312345	90003	12345

Thanks!

Answer 1

Dear respected It's MR,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description, you may use the RIGHT function and combination of the LEFT and LEN functions:

For First 5 digits:

=LEFT(A2, LEN(A2) - 4)

---

Or for First 4 digits:

=LEFT(A2,LEN(A2)-5)

Left digits:

=RIGHT(A2, 5)

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad

Answer 2

In B2:

=LEFT(A2,LEN(A2)-5)

or if you want the result to be a number

=--LEFT(A2,LEN(A2)-5)

In C2:

=RIGHT(A2,5)

or

=--RIGHT(A2,5)

Fill down.

Answer 3

Thank you both. This is perfect. =)