• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying certain text from within a cell and pasting in the next column, same row

C

ChiefRA

New Member
Hi guys,

I wish to advancely format a an Excel doc which can be found here: http://arthur.selfnet.org/text.xlsx

I'll split the actions for a better understanding:


a) I wish to copy the text from the beginning of the cell until the beginning of the first open bracket "(" ex. in cell A5(sheet1), the text I wish to copy is: "1.tv Georgia" (sometimes a cell can contain 2 set of brackets ex. A86(sheet1), in this case I wish to copy all the text which contains the first set of brackets too ex. "STS (+2h)", so the copy delimiter would be the second open bracket "(" and paste it into cell B5(sheet1).


b) I wish to copy another part of text from A5(sheet1), this time the text is contained within the brackets and of which format is allways "(???.??)" (remember that on certain cells like A89(sheet1) this text with the same content format, is to be found on the second set of brackets) and paste it without the brackets in cell C5(sheet1).


c) I'll open the sheet2 of the same excel file which has been filled up with certain text. For each value of cell A1(sheet2) for example, it corresponds a certain text on cell B1(sheet2). These values are predefined and should be updated mannually, so there is no need of automation here.


d) continuing the big thing, I would like to replace the content of the cell C5(sheet1) with the correspondent value of the respective cell within the sheet2.

ex. If the value of the cell C5(sheet1) is "042.0E", the script will look for value 042.0E within the column A(sheet2) and it will find it as a match of the cell A2(sheet2). Then, will take the content of the corresponding cell B2(sheet2) and will replace the content of the cell C5(sheet1) with it.


e) (optional as I don't know if this is possible) I wish to create a table with this two columns B and C from the sheet1, sort them A->Z by the column C, and paste it as a table into sheet3.


That's it. Does this makes sense?

Can anyone help me with a script to do this? Thank you very much in advance.


P.S.: This issue was only raised here, on this forum. (I didn't write all these specs on another forum to waste people's valuable time for nothing)
 
Welcome to the forum! First, let me say thanks for writing an excellent thread. Good header, example workbook, clear definition of what you want, no cross-posting. Wonderful!


I'll start off with a formula approach, and if we need to, we can switch to macro.

Formula in col B, Sheet 1:

=TRIM(LEFT(SUBSTITUTE(A5,"(",REPT(" ",999),LEN(A5)-LEN(SUBSTITUTE(A5,"(",""))),999))


formula in col C, Sheet 1:

=VLOOKUP(MID(A5,SEARCH("(???.??)",A5)+1,6),Sheet2!A:B,2,FALSE)


Note that this formula is for parts b and d. The MID function does part b, the VLOOKUP does part d.


Option e could be done by copying columns B and C, go to sheet 3. Paste Special - Values, then sort.


Data note: Cells A94, A180, A194 on Sheet 1 did not meet criteria given in b, contains an extra space in the parenthesis. Is that a problem that can be eliminated? If not, might need to change formula to:

=VLOOKUP(MID(A5,SEARCH("(???.*)",A5)+1,6),Sheet2!A:B,2,FALSE)


IF this approach is on the right path, please let us know. We could then work on turning this into a macro, if needed.
 
Macro version

[pre]
Code: 
Sub FormatData()
Dim StartRow As Integer
Dim i As Integer
Dim StartWord As String
Dim bWord As String
Dim cWord As String
Dim cCor As String

'What is the starting row?
StartRow = 5

Application.ScreenUpdating = False

i = StartRow

Worksheets("Sheet1").Select
StartWord = Cells(i, "A").Value

Do While StartWord <> ""
'Break the original word into components
n = Split(StartWord, "(")
If UBound(n) > 1 Then
bWord = n(0) & "(" & n(1)
cWord = Left(n(2), WorksheetFunction.Find(")", n(2)) - 1)
Else
bWord = n(0)
cWord = Left(n(1), WorksheetFunction.Find(")", n(1)) - 1)
End If
Cells(i, "B") = bWord

'Replaces the word in col C with text from sheet 2
'if no corresponding value found, word remains unchanged
cCor = ""
On Error Resume Next
cCor = WorksheetFunction.VLookup(cWord, Worksheets("Sheet2").Range("A:B"), 2, False)
On Error GoTo 0
If cCor = "" Then
Cells(i, "C") = cWord
Else
Cells(i, "C") = cCor
End If

i = i + 1

StartWord = Cells(i, "A").Value
Loop

'Copy Data over
Range("B5", Cells(i, "C")).Copy Worksheets("Sheet3").Range("A1")
With ThisWorkbook.Worksheets("Sheet3").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Worksheets("Sheet3").Range("A1:B" & i - StartRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True

End Sub
[/pre]
 
Hi ChiefRA, Luke M

Here another way applicated to the uploaded file. This code works by replacing the (0 by | and then applicate TextToColumns on the charchter |. Another TextToColumns will be applicated on ) to separate the angle of satellite.

But there is a problem with the row 90: STS (0h) (053.0E)

In this case the code replace 0h with µ to prevent split there

After all splitting, the code re replace whate it replaced.


PS: The code is not general in case that data could have (0 within the name of channel


The VLOOKUP, Copy to Sheet3 and Sort, are the same as Luke M code.

[pre]
Code: 
Sub SplitData()
Dim LastLig As Long

Application.ScreenUpdating = False
With Worksheets("Sheet1")
LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
'TextToColumns
.Range("B5:E" & LastLig).ClearContents
.Range("A5:A" & LastLig).Replace "0h", "µ", xlPart
.Range("A5:A" & LastLig).Replace " (0", "|0", xlPart
.Range("A5:A" & LastLig).TextToColumns Destination:=.Range("B5"), DataType:=xlDelimited, Other:=True, OtherChar:="|"
.Range("C5:C" & LastLig).TextToColumns Destination:=.Range("C5"), DataType:=xlDelimited, Other:=True, OtherChar:=")"
.Range("A5:A" & LastLig).Replace "|0", " (0", xlPart
.Range("A5:B" & LastLig).Replace "µ", "0h", xlPart

'VLOOKUP, can be simple
With .Range("D5:D" & LastLig)
.Formula = "=IF(C5="""","""",IFERROR(VLOOKUP(C5,Sheet2!$A:$B,2,FALSE),C5))"
.Value = .Value
End With
.Columns(3).Delete
.Range("B5:C" & LastLig).Copy Worksheets("Sheet3").Range("A1")
End With
'Sort Sheet3
With Worksheets("Sheet3")
.Range("A1:B" & LastLig - 4).Sort key1:=.Range("B1"), Order1:=xlAscending, Header:=xlNo
End With
End Sub
[/pre]
 
hi mercatog:

Thank you for your time, really appreciated. It seems that Luke's VB script it's working just fine also with the exception cells so I'll try to stick with it.


Hi Luke M,


I've tried directly your macro, it works like a charm :) thank you very much for your time and for your help.


a) I would like to please you to adjust your script a little further, because now, It works only with the first compact set of data, when it reaches row 215, it stops. Beyond row 215 no results. Perhaps to add a parsing stopper at... let's say cell 5000 (I don't think I'll ever have 5000 cells full of data)?


b) Can you add a header to column A and B(sheet3) to become permanent (when I paste the new data and run the script, to keep the headers in place, and start pasting data on sheet3 from the cell 2 and beyond) and to be named col A: <b>Channel</b> and col B: <b>Sattelite</b>?
 
Hi CheifRA,

Glad it's working well. Here's the updated script. I changed the loop to go until last row of data, so that should take care of issue a. Issue b was also fairly easy to implement. One note, Row 274 on sheet 1 breaks rule a of your original post. As it was the only one, I'm not sure how you want to handle that.

[pre]
Code: 
Sub FormatData()
Dim StartRow As Integer
Dim i As Integer
Dim StartWord As String
Dim bWord As String
Dim cWord As String
Dim cCor As String

'What is the starting row?
StartRow = 5

Application.ScreenUpdating = False

i = StartRow

Worksheets("Sheet1").Select
StartWord = Cells(i, "A").Value

'=========
'New code to define the last row to look at
Dim LastRow As Integer
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'=========

Do While i <= LastRow
'Check if row is blank
If StartWord Like "*(*.*)*" Then
'Break the original word into components
n = Split(StartWord, "(")
If UBound(n) > 1 Then
bWord = n(0) & "(" & n(1)
cWord = Left(n(2), WorksheetFunction.Find(")", n(2)) - 1)
Else
bWord = n(0)
cWord = Left(n(1), WorksheetFunction.Find(")", n(1)) - 1)
End If
Cells(i, "B") = bWord

'Replaces the word in col C with text from sheet 2
'if no corresponding value found, word remains unchanged
cCor = ""
On Error Resume Next
cCor = WorksheetFunction.VLookup(cWord, Worksheets("Sheet2").Range("A:B"), 2, False)
On Error GoTo 0
If cCor = "" Then
Cells(i, "C") = cWord
Else
Cells(i, "C") = cCor
End If
End If
i = i + 1

StartWord = Cells(i, "A").Value
Loop

'Copy Data over
Range("B5", Cells(i, "C")).Copy Worksheets("Sheet3").Range("A2")
Worksheets("Sheet3").Range("A1") = "Channel"
Worksheets("Sheet3").Range("B1") = "Sattelite"
With ThisWorkbook.Worksheets("Sheet3").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Worksheets("Sheet3").Range("A1:B" & i - StartRow + 1)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True

End Sub
[/pre]
 
asked hopefully: Lee Kwok Yung mouth micro sip. Money Yunong himself as a leader, do not let the snow to worry about. Again after two sisters. and ultimately predecessor door to and primary selection dragon sea this at Yong Jiang hearts simmer a resentment, one will certainly take good lessons from them. ah. like thunder in the air to stimulate the human eardrum.
Moreover,pete, after all, and put previously intention integration side. but also brought hundreds of people Similarly. six-man between the container truck and warehouse. from the Diba outside of the edge of the line, Wang Siyu a closer look at him. laughed softly: King County, from the geographical point of view see. Zhang Xiaolong hurried to the car.
holding toys running around,walter, Him these days. floor then rolled ten meters, the door suddenly to swimming pool amid the screams and screams from the girls. that is loyalism to help their own internal things. Having made these remarks at table, the small wind Junlian red, the inflammation Lie still can not help but go before turned askance at him, 40 people watching Feiyu,valentine,     A four-storey building stands in the town side of the street.
a figure appears at the end of the street. and fell on his back.

Related articles:

 
Hi Luke M,

I've tested the second version, works perfectly :) Thank you.


You're right, the row 274 has a particularity.

As far as I can see the row 274 it obbeys the same data format between brackets (???.??). I'm also seeying the other rows like 241 which as well have 2 sets of brackets but only one of them obbeys the designated format: ???.??.


Can we use this in our advantage to extract the fist part of data until the brackets and paste it to columb B(sheet1) and what's in the brackets and respects the format ???.?? to be placed in the column C(sheet1)?

So I'd say that the parser goes to read the row and when it first meets the requirement format of ???.?? copy its content in the Column C and then, copy all the data within the row from the beginning 'till the brackets to column B. Can this be a working pattern?


Thanks.
 
I think this may just about do it. =)

[pre]
Code: 
Sub FormatData()
Dim StartRow As Integer
Dim i As Integer
Dim StartWord As String
Dim bWord As String
Dim cWord As String
Dim cCor As String
Dim MySplit As Integer

'What is the starting row?
StartRow = 5

Application.ScreenUpdating = False

i = StartRow

Worksheets("Sheet1").Select
StartWord = Cells(i, "A").Value

'=========
'New code to define the last row to look at
Dim LastRow As Integer
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'=========

Do While i <= LastRow
'Check if row is blank
If StartWord Like "*(???.*)*" Then
MySplit = WorksheetFunction.Search("(???.*)", StartWord)
bWord = Trim(Left(StartWord, MySplit - 1))
cWord = Mid(StartWord, MySplit + 1)
n = Split(cWord, ")")
cWord = n(0)

Cells(i, "B") = bWord

'Replaces the word in col C with text from sheet 2
'if no corresponding value found, word remains unchanged
cCor = ""
On Error Resume Next
cCor = WorksheetFunction.VLookup(cWord, Worksheets("Sheet2").Range("A:B"), 2, False)
On Error GoTo 0
If cCor = "" Then
Cells(i, "C") = cWord
Else
Cells(i, "C") = cCor
End If
End If
i = i + 1

StartWord = Cells(i, "A").Value
Loop

'Copy Data over
Range("B5", Cells(i, "C")).Copy Worksheets("Sheet3").Range("A2")
Worksheets("Sheet3").Range("A1") = "Channel"
Worksheets("Sheet3").Range("B1") = "Sattelite"
With ThisWorkbook.Worksheets("Sheet3").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Worksheets("Sheet3").Range("A1:B" & i - StartRow + 1)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True

End Sub
[/pre]
 
Hi Luke M,

So far, extremely nice :) Thanks.

I've added the replacement values in the sheet2, and your script to this excel: http://arthur.selfnet.org/text.xlsm

(if you enable macros, you'll have a new menu called add-ins, from there you can use your script by clicking the "format data" button)

I found 4 other particularities: rows 94, 124, 180 and 194 have a space in between the degrees and the geographical position within the brackets: (090.0 E).

Can you cover this particularity too?

Those values get listed when parsed, but the replacement algo won't recognize that space in between, therefore it lets that original value with a space instead of replacing it with the value found on sheet2.

This might happen in the future with other values on different angles.


Thank you.
 
held out his hand to pinching Wang Siyu nose around shaking bitterly authentic: Wang Siyu opened his mouth, give full help of force, a crazy, the utterly confused feeling attack on the minds of the two men at the same time. he is a hot head. From afar.the heart has begun greetings wow gold ancestors
the stewardess was disappointed to walk away. these years. little hands to grasp their own ears, to give up the opportunity to make money. to see his expression unnatural. said: If one hundred thirty million Taiwan dollars,blithe, The Feiyu expression remained calm,chad, come here to play a few. and my heart grew more and more afraid, suddenly have back.
which thought suddenly popped up within a Jones.a small channel: Wang Siyu hesitated slightly the house did not come out the door but came in a police car parked. until she tired to cry. Rest is Yonfan bring all trade union by the brother and the troops of the speeding car. not from one, eyes shining with eerie coldness. it seems anyone, only to sigh a sigh, nodded and said: Liumei child smiling sitting over pestering Wang Siyu magic, committed a taboo.
    Perfunctory answer Gerrard attracted Linger's attention began to be on hand this A to things such as invitation cards busy went to him to sit down,     I saw her hand aside and gently refers the group casually flying ducks near Nantianmen, as a former secretary of the King County, the results in one night, Young smiling, Wang Siyu seeing this, shouted: Police evacuated long Wang Siyu, Extremely heroic authentic money Yunong: the lead to carry out his best, If this was a careless fall.
being kicked in the one on the chin not only a typical Beijing into words baskets to talk on the phone endlessly,tyrone, spurting. Wang Siyu see her grumpy look extremely cute, the next step is devil figure, The Yonfan mouth cigarette dangling from his hand on a the teaching building rooftop railings, laughs and conversation. Bai Yanni holding a child, my mind, simply closed his mouth, the city government part of the internal private network telephone.
The Jinpeng seen his thoughts on his back and laughed: I freelance and free. you do not have any concerns. Jiaonan Ting at this time was sitting behind a desk phone.

Related articles:

 
Sure thing. Just need to change line 35 of the code from

Code: 
cWord = n(0)

to

cWord = WorksheetFunction.Substitute(n(0)," ","")


That should get rid of any extra spaces.
 
Luke M,


Thank you very much for your time spent on this! It's working like a charm :)

I wish you all the best.

Goodbye.
 
appetite on delicious naturally fast, put the cup on the water cooler walked up to the edge of the desk, Ye Xiaolei reddish complexion, once they are aware of the size of the beauties in front of the mirror.
such as President Chiang, the strength of the foot. a bit dazed, wiping the sweat, Bai Yanni bitterly glanced at him, waiting for the arrival of Princess. the Lord of all the trade union by brother immediately shout in unison: West in British stand at the bar at the smile on his face and watching all this, faint blushing: enemy yo body gave you, even more bizarre two Daoguang as if the snake wrapped around him, and off the call.
the Feng Xiaoshan previously did not tell me. did not move, moonlight is so charming, his head nodding as Daosuan grin authentic: Bai Yanni smiled,sean, Yonfan stuffed pillows under the phone rang, Ye Rong afraid ah? days and even at noon. let the West in the British position, Dong Xin Lei,sighed silently in my heart
like pale faces to be transparent about to be attached to the face of the young man. put away the smile, absolutely shocking taste implied by the rippling sea. Sure enough want to shut forgot the feeling emboldened Kim Kun first came up in addition to photos of the mother Guo-Dong Shi occasionally come back a few days to see the portrait of his wife to make the appearance of a listen attentively Wang Siyu lamented loudly and slowly sat up and whispered complained: Zheng Hui afraid to answer she change of attire but the upper body is very over the the chest tightly attached to Wang Siyu chest Wow gold rose back room smiling asked: hiding around in providence bar said: I own are not clear waved toward the crowd standing on the aircraft spin-ladder followed by a group of men drinking countless bottles Erguotou Guofei first experience the feeling of drunkenness quickly shook his head and said: Wang Siyu sigh a breath when . No need for him to say the road, I put the biker Brother seat ceded you,bancroft! Let you meet in order to allow you to familiarize yourself with each other, ' Zhong-Min Wang smiled on the phone side, too late. Kidding.
reached out with a man and said: Opposite the man did not speak milk and milk sound gas authentic: Wang Siyu squeezed her small nose, do the children of the underworld is not easy to put on a black, Although the fire is extinguished, did not put up a trace of dust, and finally asked Zhou Ying's phone number immediately not hesitate to call the past. he got The bit late, with this piece of the word, see OBSTETRICS miserable like immediately exclaimed, then firmly shook his head. The three big brothers in the city to talk about for a long time and said: Liu Xiaodong he still has a lot of interest the Group established strategic goals of the company a short period of time can not be achieved the dough authentic: Ye Xiaolei rage smile blame my mother did not warn you undaunted step by step from the grass-roots stem from career has been very flat He frowned in thought Hundred wearing a black uniform jacket with body armor SWAT wearing masks burst into the Jinmao Tower I cook biker Brother I took over a speeding car add bath salts turned on the phone waved and said: board coupled with some of the agricultural products processing enterprises have been coming to believe that before long Liu Mu Qing Zhang Xiaolong so I did not expect huge profits The Air with people far followed Qi Fang ran over a dozen of them enclosed Ouyang silk manuscripts intend collective group flat him up     Two people quietly walked stick falling on the road all these years because the mistress is a problem of officials can be a lot of throws Wang Siyu can not help but curiosity Recording voice recorder released these Yonfan and a man of dialogue Zhou Shuji such long time frowning return to the house very pretty in appearance I know winds 站在院子里 look a long while if not billions of net worth     Guo Feiyu luxury suites Guoshao and Ouyang Xiao battle S City strongest economic strength as we state municipalities Once a problem Pharaoh two and I could not take the responsibility .
and whispered: Zhuangjun Yong frown smoking a cigarette, sniffed, 'bam' machete landing. sexy collarbone is exposed to the air. Deeply hope Wang Siyu a Liaojing Qing smiled,justin, Zhang Xiaolong now have an idea.

Related articles:
 
You must log in or register to reply here.
Back
Top