Excel selecting Sub-string from String

[help_for_excel]

Hi


String 1: "bold:yes";color:green;font >12; chart =no, graphic = bad
String 2: "bold:yes";colorurple;font >100; chart =no, graphic = bad
String 3: "bold:no";font >8; chart =no, graphic = very good;color:brown

my question is I want VBA code or excel formula which outputs
color:green
colorurple
color:brown

I know Mid function can be used but I am having trouble handling it. As color can appear anywhere in the string and I want whole things "Color: green"


Please help, thanks in advance

 

[adam087]

Perhaps try this?
=MID(A2,FIND("color", A2),IFERROR(FIND(";", A2, FIND("color", A2))-FIND("color", A2), 255))

Regards
Adam

 

[Jonmo1]

Try

=MID(A1,FIND("color:",A1),FIND(";",A1&";",FIND("color:",A1))-FIND("color:",A1))

 

[help_for_excel]

Guys its not doing as it is suppose to as

color : green

but I cannot paste html code because it is onverting to graphich module

 

[Jonmo1]

OK,.... What?

 

[help_for_excel]

Color is not always ended by :

there can be

color: green;
colorurple'
color:red<

I want function to take all this into consideration

 

[Jonmo1]

Do you have or can you create a list of all the possible colors that may appear in the string?

If so, try

="color:" &LOOKUP(2^15,SEARCH($E$1:$E$6,A1),$E$1:$E$6)

A1 is the string
$E$1:$E$6 is a list of possible colors

 

[jraj1106]

Try this piece of code:

Sub EXTRACTCOLOUR()
Dim rcnt As Long, finalval As String, i As Long

rcnt = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To rcnt
temp = InStr(1, Range("A" & i).Value, "color")
temp1 = InStr(temp, Range("A" & i).Value, ";")
If temp1 <> 0 Then
temp1 = temp1 - temp
finalval = Mid(Range("A" & i).Value, temp, temp1)
Else
finalval = Mid(Range("A" & i).Value, temp)
End If

Range("B" & i).Value = finalval
temp = 0
temp1 = 0
Next
End Sub

Regards...

 

[help_for_excel]

it doesnt work

below line gives me error

temp1 = InStr(temp, Range("A" & i).Value, ";")

error: invalid procedure call or argument

 

[Firefly2012]

Hi

Perhaps a UDF:

Function GetColor(s As String)
With CreateObject("vbscript.regexp")
  .Pattern = "\bcolor\:.+?\b"
  .IgnoreCase = True
  If .test(s) Then GetColor = .Execute(s)(0)
End With
End Function

Use in a cell like:

=GetColor(A1)

Note: it currently only returns the first match in the referenced string.