Separate first 6 numbers from the account list

[MidK]

I want to separate the first six numbers from the below account list.

Example below.

Number I want to see below
32134121103 321341 | 21103
6222311510183 622231 | 1510183
6213310561128
6259710143850
82080509137691
112879428448092
641800200669789

Which formula I should use?

Thanks

[Sam Capricci]

I would recommend =LEFT(A2,6)

oh, you may need to replace the comma with a semicolon for your locale if that doesn't work.

[MidK]

Thanks for your response. Left(A2,6) will give me only the first 6 digit of numbers, then I need the remaining numbers in the separate column.

32134121103 - Left formula will give me 32134. How do I get the remaining numbers in the separate column - 121103

Can you show me the formula for the below numbers?
6213310561128
6259710143850
82080509137691
112879428448092
641800200669789

[wk9128]

Cell B3 formula

Please Login or Register  to view this content.

Cell C3 formula

Please Login or Register  to view this content.

[alansidman]

With Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Excel 2016 (Windows) 64 bit

	A	B	C	D
1	Column1		Column1.1	Column1.2
2	6213310561128		621331	0561128
3	6259710143850		625971	0143850
4	82080509137691		820805	09137691
5	112879428448092		112879	428448092
6	641800200669789		641800	200669789

Sheet: Sheet1

[wk9128]

MidK You're Welcome. Glad to help . Thank You for the feedback and rep.