HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT
 

I HAVE STYLE NUMBERS THAT HAVE UP TO 11 CHARACTERS
BUT I NEED TO SEPERATE THEM AS 6 IN THE FIRST FIELD AND THE BALANCE OF 1, 2,
3, 4, OR 5 CHARACTERS IN THE 2nd FIELD.
EXAMPLES:
123456BLUE = 123456 BLUE
123456BLACK = 123456 BLACK
123456ASST = 123456 ASST

Assume your entries are in col A. Use this in col B

=LEFT(A1,5)

Use this in col C

=TRIM(RIGHT(A1,LEN(A1)-5)))


"GRYSYF" wrote:

I HAVE STYLE NUMBERS THAT HAVE UP TO 11 CHARACTERS
BUT I NEED TO SEPERATE THEM AS 6 IN THE FIRST FIELD AND THE BALANCE OF 1, 2,
3, 4, OR 5 CHARACTERS IN THE 2nd FIELD.
EXAMPLES:
123456BLUE = 123456 BLUE
123456BLACK = 123456 BLACK
123456ASST = 123456 ASST

Firstly, posting in CAPITALS is considered SHOUTING and the post is often
ignored.

Secondly, try the solution below. It assumes your original data is in A1.

=LEFT(A1,6) gives the first 6 characters
=RIGHT(A1,LEN(A1)-6) gives everything after the first 6 characters
--
Ian
--
"GRYSYF" wrote in message
...
I HAVE STYLE NUMBERS THAT HAVE UP TO 11 CHARACTERS
BUT I NEED TO SEPERATE THEM AS 6 IN THE FIRST FIELD AND THE BALANCE OF 1,
2,
3, 4, OR 5 CHARACTERS IN THE 2nd FIELD.
EXAMPLES:
123456BLUE = 123456 BLUE
123456BLACK = 123456 BLACK
123456ASST = 123456 ASST

And if you want 123456 Blue all in the same cell, try this.

=LEFT(A1,6)&" "&RIGHT(A1,LEN(A1)-6)

HTH
Regards,
Howard

"GRYSYF" wrote in message
...
I HAVE STYLE NUMBERS THAT HAVE UP TO 11 CHARACTERS
BUT I NEED TO SEPERATE THEM AS 6 IN THE FIRST FIELD AND THE BALANCE OF 1,
2,
3, 4, OR 5 CHARACTERS IN THE 2nd FIELD.
EXAMPLES:
123456BLUE = 123456 BLUE
123456BLACK = 123456 BLACK
123456ASST = 123456 ASST

Ian wrote...
....
Secondly, try the solution below. It assumes your original data is in A1.

=LEFT(A1,6) gives the first 6 characters
=RIGHT(A1,LEN(A1)-6) gives everything after the first 6 characters
....

Easier than using RIGHT call is using MID,

=MID(A1,7,1024)

Note that if LEN(A1) < 6, RIGHT(x,LEN(x)-6) returns a #VALUE! error
while MID(x,7,1024) returns "".

A non formula option

Select the codes
Data\Text to Columns..\Fixed

then put a break point in as directed
you will need space in the column to the right to accept the seperated
data.

hth RES