Extracting certain text from once cell to another

[Dean McG]

Hi

I currently have an issue with sorting data that i download into excel.

I have a list of rows that have text that look something like the below:

<TABLE style="WIDTH: 578pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=770><COLGROUP><COL style="WIDTH: 578pt; mso-width-source: userset; mso-width-alt: 28160" width=770><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 578pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=770>redeem: 141BA9835E Colour: Silver</TD></TR></TBODY></TABLE>

I want to copy only the colour at the end of each of the colours into a new cell. So in theory the formula will pick only the colour out. Below is a list of more rows as an example where it changes slightly:

<TABLE style="WIDTH: 578pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=770><COLGROUP><COL style="WIDTH: 578pt; mso-width-source: userset; mso-width-alt: 28160" width=770><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 578pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=770>redeem: C3CC237E3C Colour: Silver

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>redeem: db56b2177c Colour: Pink

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>redeem: 7d3fe7eba2 Colour: Black

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>redeem: EBE56BDA7A Colour: Silver</TD></TR></TBODY></TABLE>

To summarise have can i extract into the cell next door, only the colours from text

This is my first time posting so i hope i have made sense

Any help would be greatly appreciated. Currently i am having to manually type only the colour into a cell, and it is taking forever when you have 1000's or rows

Much appreciated

Dean

 

[Warship]

I'm much better with VBA.
Can you use VBA to do it?

VBA can do this easily, be made dynamic to fit your changing data size.
And run anytime you need it with worrying about messing up sheet formulas.
I can whip out the VBA in a few minutes after getting some specifics about the data from you. Formulas aren't really in my bag o' tricks.
Let me know if you want the VBA. Be happy to oblige.

 

[Peter_SSs]

i have to do 3-4 times a week.

In that case, test this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Extract_Colours()<br>    <SPAN style="color:#00007F">Const</SPAN> DataCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A"   <SPAN style="color:#007F00">'<-Column your original data is in</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> Sep <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "#"       <SPAN style="color:#007F00">'<- Some character not in your data</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range(DataCol & 1, Range(DataCol & Rows.Count).End(xlUp)).Offset(, 1)<br>        .Value = .Offset(, -1).Value<br>        .Replace What:=" Colour: ", Replacement:=Sep, LookAt:=xlPart, _<br>            MatchCase:=False, SearchFormat:=<SPAN style="color:#00007F">False</SPAN>, ReplaceFormat:=False<br>        .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _<br>            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _<br>            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=Sep, _<br>            FieldInfo:=Array(1, 9), TrailingMinusNumbers:=<SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Warship]

There you go again Peter, airing out that magic sleeve.
You knew darn well I was gonna Loop this didn't you.

Peter rocks, Dean, pay attention

 

[Peter_SSs]

You knew darn well I was gonna Loop this didn't you.

No, not at all.

In fact, since the Text to Columns had been suggested, I thought you may have used that. I did think any TtC suggestion might not Replace the " Colour: " text and may not specifically exclude the column(s) before the first colour so the results may not have been as tidy.

 

[Warship]

Peter,

Since we're talking upwards of 5000 lines, I'm thinking the TtC will be far superior speed wise, won't it?

Even with the TtC sug, I still didn't consider it!
I must chant now... "There are other options to Looping, There are other..."

 

[Peter_SSs]

If the data is as uniform as the samples provided, I think TtC would be very efficient. Let's hope the OP embraces a macro approach.

 

[Warship]

Let me see....

Set up a macro once then use it over and over.

OR

Figure out the formulas. Fill Down. Fix misalignments. Find them again when I (oops) deleted them. Copy them over when found. Now fix the cell references.

Not sure....

I'm thinking I'd go macro

 

[Dean McG]

Once again much apprciated for your help.

I am going to test the suggestions and see how it goes. For reference, the data is not the same each time, but the part where it is colour: (colour) is, so hopefully all goes well.

Once i have gone through I will give everyone an update

Kind Regards

Dean