Macro to extract few values from a cell

[Johin Chandresh.B]

Hello guys,

can some one please help me with a macro to extract few details from a cell and paste it in a separate cell Please?

Example:
In A1 I have Q/0 "03T6560" xxx xxxxx xxx ETA "16/08/13 09:30"; dsdsdsd Order # 2323213; Q/0 "03T6560" xxx xxxxx xxx ETA "16/08/13 09:30"; dsdsdsd Order # 2323213

In one cell I have a big sentence which contains several (upto 5) Q/0 number, ETA date and order #

I need the output to be Q/0 "03T6560" ETA "16/08/13 09:30" Order # 2323213 Q/0 "03T6560" ETA "16/08/13 09:30" Order # 2323213 in separate cells on the same line.

There are about 2000 lines in excel where I have to do this and as of now am doing it manually.

Can someone who has the knowledge of writing VBA code please help me?

Regards,
Joe

 

[Claus Busch]

Hi Joe,

Am Tue, 17 Sep 2013 10:58:14 -0700 (PDT) schrieb Johin Chandresh.B:

In A1 I have Q/0 "03T6560" xxx xxxxx xxx ETA "16/08/13 09:30"; dsdsdsd Order # 2323213; Q/0 "03T6560" xxx xxxxx xxx ETA "16/08/13 09:30"; dsdsdsd Order # 2323213

In one cell I have a big sentence which contains several (upto 5) Q/0 number, ETA date and order #

I need the output to be Q/0 "03T6560" ETA "16/08/13 09:30" Order # 2323213 Q/0 "03T6560" ETA "16/08/13 09:30" Order # 2323213 in separate cells on the same line.

try in B1:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"x",),"dsdsdsd",),";",))
and copy down. Copy the column and Paste Special => Paste Values and
delete column A


Regards
Claus B.

 

[Johin Chandresh.B]

Hi Claus,

Thanks for your reply.

The xxx and dsds are actually information which is not constant across all the cells. The actual data in the cell looks like this

Info in the cell A1

Q/O - 03T7032 QTY - 1 SYSTEM BOARD (PLANAR) ETA 16/08/2013 @ 09:00AM ORDER # - 112348135 Q/O 043N9877 QTY - 1 65W HsFGDDdEAfT SsdFGeNK FadAFfN ETA 16/08/2013 @ 09:00A M ORDER# - 178123235

Info in the cell A2

Q/O = 04W1544; QTY = 1; PART DESC = LCD PANEL; ETA = 22/08/13 11 : 00; ORDER # = 1523242233872; Q/O = 45Md32896 ; QTY = 1; PART DESC = LCD CABLE; ETA = 22/08/13 11 : 00 ; ORDER # = 15287342;

Information keeps changing and the format or the number of characters are not the same. The Q/O, QTY and ETA gets repeated upto 5 time in the same cell.

Please help.

Regards,
Joe

 

[Claus Busch]

Hi Joe,

Am Tue, 17 Sep 2013 11:30:06 -0700 (PDT) schrieb Johin Chandresh.B:

Info in the cell A1

Q/O - 03T7032 QTY - 1 SYSTEM BOARD (PLANAR) ETA 16/08/2013 @ 09:00AM ORDER # - 112348135 Q/O 043N9877 QTY - 1 65W HsFGDDdEAfT SsdFGeNK FadAFfN ETA 16/08/2013 @ 09:00A M ORDER# - 178123235

Info in the cell A2

Q/O = 04W1544; QTY = 1; PART DESC = LCD PANEL; ETA = 22/08/13 11 : 00; ORDER # = 1523242233872; Q/O = 45Md32896 ; QTY = 1; PART DESC = LCD CABLE; ETA = 22/08/13 11 : 00 ; ORDER # = 15287342;

try:

Sub Test()
Dim strTmp As String
Dim strRep As String
Dim LRow As Long
Dim rngC As Range
Dim myStart As Integer
Dim myEnd As Integer

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
strTmp = rngC
Do
myStart = InStr(strTmp, "QTY")
myEnd = InStr(myStart, strTmp, "ETA")
strRep = Mid(strTmp, myStart, myEnd - myStart)
strTmp = Replace(strTmp, strRep, "")
Loop While InStr(strTmp, "QTY") > 0
Do
myStart = InStr(strTmp, ":") - 4
myEnd = myStart + 9
strRep = Mid(strTmp, myStart, myEnd - myStart)
strTmp = Replace(strTmp, strRep, "")
Loop While InStr(strTmp, ":") > 0
rngC.Offset(0, 1) = Trim(Replace(strTmp, ";", ""))
Next
End Sub


Regards
Claus B.