Thanks:  0
Likes:  0

# Thread: Split out an element of lines of numbers

1. ## Split out an element of lines of numbers

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

2. ## Re: Split out an element of lines of numbers

Hi,

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

ABCD
1Region CodeCustomer #
211111177777777772017-12-111111117777777777
312345678901234562017-11-121234567890123456

Sheet1

Worksheet Formulas
CellFormula
C2=LEFT(A2,6)
D2=MID(A2,7,10)

C2, D2 formulas copied down.

3. ## Re: Split out an element of lines of numbers

Perfect - thank you

4. ## Re: Split out an element of lines of numbers

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.

5. ## Re: Split out an element of lines of numbers

Originally Posted by philb99
Perfect - thank you
You're welcome.