Splitting one cell into two

[Louise]

Dear all

I have a large Excel worksheet that contains data in
column A and has approximately 4000 rows. Each cell
contains a product number and a product description. How
can I ask Excel to separate the two - so column A
contains the code and Column B contains the description?

I've tried the text to columns option but it doesn't seem
to work.

Any urgent ideas would be appreciated.

Thank you.

Louise

 

[Domenic]

Hi Louise,

Can you give us an example of what you have so that we have something to
work with?

 

[Debra Dalgleish]

If all product numbers are the same length, the Fixed Width option in
step one of the Text to Columns dialog box should work.

In Step Two, double-click on all lines except the first one.

 

[Louise]

problem is, the product numbers are not the same length.
any more suggestions/??

 

[Debra Dalgleish]

Assuming your data starts in cell A1, enter the following formula in
cell B1:
=LEFT(A1,SEARCH(" ",A1)-1)

In C1: =RIGHT(A1,LEN(A1)-(LEN(B1)+1))

Copy these formulas down to the last row of data.

Select columns B and C, and choose Edit>Copy
Choose Edit>Paste Special
Select Values, click OK
Delete column A

 

[Louise]

my worksheet basically looks like:-

A B

01 Karen Oldman
200 Jane Smith
4015 Sarah Parker

All the data is held in column A, with an employee number
in front of each name but not all the numbers have the
same numbers of characters. I need the employee number
only to be in Column A and the name to be in Column B.

If you would prefer, let me know your e-mail address and
i will forward the worksheet.

Thanks.

Louise

 

[Louise]

my worksheet basically looks like:-

A B

01 Karen Oldman
200 Jane Smith
4015 Sarah Parker

All the data is held in column A, with an employee number
in front of each name but not all the numbers have the
same numbers of characters. I need the employee number
only to be in Column A and the name to be in Column B.

If you would prefer, let me know your e-mail address and
i will forward the worksheet.

Thanks.

Louise

 

[Domenic]

Hi Louise,

It looks from you sample that there are 2 spaces between the employee
number. If that's the case, then try the following:

Employee Number

=LEFT(A1,FIND(" ",A1)-1)

Name

=MID(SUBSTITUTE(A1," ","",1),FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))

If there's only one space between the 2, then for the Name try,

=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))

Hope this helps!

 

[Soo Cheon Jheong]

Louise,

1) Create the following worksheet

A B C D
----------------------------------------------------------------------
1 Data Product Number Product Description Temporary
2 123ABCDE ? ? ?
3 556677BBDCC
4 777999FFGG
5
..
..
..
4001
----------------------------------------------------------------------

2) Enter three Formulas

B2: =SUBSTITUTE(A2,C2,)

C2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(D2,7,),8,),9,),0,),".",)

D2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(A2,1,),2,),3,),4,),5,),6,)

These formulas produce three string values


3) Select the range: B24001

4) Press CTRL + D

5) Press CTRL + C

6) From the Edit menu or the shortcut menu, choose Paste Special.

7) Under Paste, select the Values option button.

8) Choose OK button

9) Delete Column D and then Column A.


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Louise]

This seems to have worked fantastic.

Thank you very much.

Louise