Seperate data in a cell

[jschoon4]

I Need to separate the item # / from the description in column A? I have about 2000 rows. Once the number is removed I need to place that number in Column B next to the description.


Ex.
A
[TABLE="width: 526"]
<tbody>[TR]
[TD="width: 526"]1 4986238 / SHOE ORTHOWEDGE FOREFOOT OPEN TOE OL-M[/TD]
[/TR]
</tbody>[/TABLE]
2 6120067 / CABLE EKG SPLB 5 LEAD 2-PRONG SAFETY 700-0008-06

[TABLE="width: 526"]
<tbody>[TR]
[TD="width: 526"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 526"]
<tbody>[TR]
[TD="width: 526"][/TD]
[/TR]
</tbody>[/TABLE]

 

[tindorji1]

Is the item number always located at the beginning of the string? Is the item number always 7 characters long?

If so, the LEFT formula should work: =LEFT(A1,7) where A1 is the cell where your data is stored, and 7 is the number of characters you want to return

 

[mumps]

Try this macro:

Sub removeNum()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim num As Variant
    Dim rng As Range
    For Each rng In Range("A1:A" & LastRow)
        num = Split(rng, "/")
        rng.Offset(0, 1) = Trim(num(0))
        rng = Trim(num(1))
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Oeldere]

Book1
	A	B	C	D
13	4986238 / SHOE ORTHOWEDGE FOREFOOT OPEN TOE OL-M	SHOE ORTHOWEDGE FOREFOOT OPEN TOE OL-M		4986238
14	6120067 / CABLE EKG SPLB 5 LEAD 2-PRONG SAFETY 700-0008-06	CABLE EKG SPLB 5 LEAD 2-PRONG SAFETY 700-0008-06		6120067
15	1412 / new tekst to test etc	new tekst to test etc		1412
Blad24

Cell Formulas
Range	Formula
B13	=RIGHT(A13,LEN(A13)-SEARCH("/",A13,1)-1)
D13	=LEFT(A13,SEARCH("/",A13,1)-2)

 

[Scott Huish]

You could also do Data..Text to Columns with / as the delimiter