Extract text from a cell and put it in another cell

[baindes]

Hi there,

I am trying to give attributes to item descriptions that dump from a database into excel. There are several different attributes I need to pull from each item description. I need to pull size, and type and put that into another cell in another column to sort off of later. The data dumps with a description like this in each cell: 774587 cleaning product 6OZ Plastic. Another one may say: 778498 cleaning product 10OZ Glass. How do I pull the size and container type out of about 45k rows and dump that into the cell next to it? I want size in one column and type in the next column.

Regards~

 

[barry houdini]

Will the size and type always be the last two "words"? If so perhaps like this

Book1
	A	B	C	D
1	778498 cleaning product 10OZ Glass	10OZ	Glass
2	774587 cleaning product 6OZ Plastic	6OZ	Plastic
3
4
5
Sheet1

Formula in C1 copied down column

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

Formula in B1 copied down column

=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A1,C1,""))," ",REPT(" ",99)),99))

 

[sweater_vests_rock]

hi baindes.

welcome to the board!

try this macro out. be sure to change SourceColumn and StartRow to the appropriate settings before running the program. also, please run on a copy of your file -- macros, once run, may not be undone.

hope this helps.
ben.

Sub SplitLastTwo()

Dim StartRow As Long, SourceColumn As Integer
Dim SourceRange As Range, OutputRange1 As Range, OutputRange2 As Range
Dim SourceArray(), OutputArray1(), OutputArray2()

Dim i As Long

StartRow = 2 '<-- Change this to the first row of your dataset
SourceColumn = 3 '<-- Change this to the column containing the text to break apart

Application.ScreenUpdating = False

Set SourceRange = Range(Cells(StartRow, SourceColumn), Cells(Rows.Count, SourceColumn).End(xlUp))

SourceArray = SourceRange
ReDim OutputArray1(LBound(SourceArray) To UBound(SourceArray), 1 To 1)
ReDim OutputArray2(LBound(SourceArray) To UBound(SourceArray), 1 To 1)
For i = LBound(SourceArray) To UBound(SourceArray)
    OutputArray1(i, 1) = Split(SourceArray(i, 1), " ")(UBound(Split(SourceArray(i, 1), " ")) - 1)
    OutputArray2(i, 1) = Split(SourceArray(i, 1), " ")(UBound(Split(SourceArray(i, 1), " ")))
Next i

SourceRange.Offset(0, 1).EntireColumn.Insert
SourceRange.Offset(0, 1).EntireColumn.Insert

Set OutputRange1 = SourceRange.Offset(0, 1)
Set OutputRange2 = SourceRange.Offset(0, 2)

OutputRange1 = OutputArray1
OutputRange2 = OutputArray2

Application.ScreenUpdating = True

End Sub

ps. there is no error checking in this, which means if you have fewer than two spaces in the cells you are trying to split, the code will error-out.

 

[barry houdini]

Re your PM.....to get just the material if present

Borrowing from a formula posted by Domenic tonight..... you could try something like this

Book1
	A	B	C	D	E
1
2	778498 cleaning product 10OZ Glass	Glass		PVC
3	774587 cleaning product 6OZ Plastic	Plastic		Plastic
4	123456 wood container 12OZ	Wood		Wood
5				Metal
6				Glass
7
Sheet1

formula in B2 copied down column

=INDEX($D$2:$D$6,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$6,A2)),0),0))

where your 5 possibilities are in D2:D6

 

[baindes]

thank you...perfect! I appreciate your time on this.