Split out an element of lines of numbers

[philb99]

Hi - I have received a block of data which has surrounded the key information that I require.

Let me explain 11111177777777772017-12-11 is recorded in a column called Customer Number

I require the Customer Number which the ten 7's, of course they not be 7 as each customer will have a unique customer number.

How can I create a separate column by extracting this information. It would also be good to get the Region code which the Six 1's into another column

As you can see the final 6 numbers reflect the date and I do not need

Any help greatly appreciated.

Thanks

 

[jtakw]

Hi,

If your Region Code is always 6 digits, and your Customer Number is always 10 digits:

Book1
	A	B	C	D
1			Region Code	Customer #
2	11111177777777772017-12-11		111111	7777777777
3	12345678901234562017-11-12		123456	7890123456
Sheet1

Cell Formulas
Range	Formula
C2	=LEFT(A2,6)
D2	=MID(A2,7,10)

C2, D2 formulas copied down.

 

[philb99]

Perfect - thank you

 

[mrhstn]

You could also use Text to Columns

1. Data>>Text to Columns
2. Fixed Width >> Next
3. Click to enter split points before and after the customer ID >> Next
4. Click any columns you don't want (i.e the 'date' part and the first part) and choose don not import
5. If you want to replace the long number leave the destination be, if not choose an adjacent column >> Finish

You data is now split.

 

[jtakw]

Perfect - thank you

You're welcome.