Split out an element of lines of numbers

philb99

Active Member
Joined
Feb 3, 2014
Messages
385
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

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


Book1
ABCD
1Region CodeCustomer #
211111177777777772017-12-111111117777777777
312345678901234562017-11-121234567890123456
Sheet1
Cell Formulas
RangeFormula
C2=LEFT(A2,6)
D2=MID(A2,7,10)


C2, D2 formulas copied down.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top