How to set Vlookup from a string of text in a cell and return the same manner

could you post an example workbook

Alright, here's the attached. Hope you can help me this.

Thank you so much for the help! I will give this a try tomorrow when I'm back in my office as all my files are in my company's computer.. This should work... Yay, I'm so happy cos my problem is solved!

Hi, i am stucked here. How do i add your code into mine? I have tried and some rules actually clashes the rules that you have set. And when I try to run the code that you gave me, it actually gives me this error message

This is my current code that I need

Sub FormatNow()
'
'Format Now Macro

Dim temp2 As Range
Dim iLastRow As Long
Dim LastRow As Long
Dim Raw As Worksheet
Set Raw = Worksheets("Raw")

With Range("B:B")
iLastRow = .Cells(Application.Rows.Count, 1).End(xlUp).Row

For i = 4 To iLastRow
temp1 = ""
Set rRange = .Cells(i, 1)

If InStr(1, rRange, "(") = 0 Then
nlen = 0
temp1 = rRange
Else
nlen = Len(rRange)
End If

Do While nlen > 1
nlen = Len(rRange)
found1 = InStr(1, rRange, "(")
found2 = InStr(1, rRange, ")")

temp1 = temp1 + Left(rRange, found1 - 1)
rRange = Right(rRange, nlen - found2)

If InStr(1, rRange, "(") = 0 Then
nlen = 0
End If
Loop

Set temp2 = .Cells(i, 1)
temp2 = temp1
Next i
End With
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B3").Select
ActiveCell.FormulaR1C1 = "Description"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Trebuchet MS"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("D3").Select
ActiveCell.FormulaR1C1 = "Platform"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Trebuchet MS"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

LastRow = Raw.Cells(Application.Rows.Count, 1).End(xlUp).Row

Range("B4").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Item Branch'!C[-1]:C,2,0)"
Range("B4").Select
Selection.AutoFill Destination:=Range("B4:B" & LastRow)
Range("B4:B" & LastRow).Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I need the vlookupmany to happen in D4

here's the attached as example

Sorry about that as I thought I can apply your rules after my own macro. Anyway, thanks a million!