Delete all text except a set of values from a column

[Thom_vee]

Hi,
I had posted this yesterday but the post has disappeared for some reason. So posting again. Apologies to Fennek and Special-K99 who replied yesterday.

I have a sheet with one column containg some descriptive text:

	A	B
1	Dark brown marked with bright yellow spots.
2	Black marked with bright yellow spots.
3	Blue marked with greenish yellow markings.
4	Brown marked with bright yellow spots.

<tbody>
</tbody>

I have some reference values: eg: Black, Blue, Yellow, Brown, Red, Blue green, ....and about 145 other values

Is there some way to delete all words in A except for the reference values?

Desired output:

	A	B
1	Dark brown marked with bright yellow spots.	brown, yellow
2	Black marked with bright yellow spots.	black, yellow
3	Blue marked with greenish yellow markings.	blue, yellow
4	Brown marked with bright red spots.	brown, red

<tbody>
</tbody>

 

[Aladin Akyurek]

If you are on a 2016 system, in B1 control+shift+enter, and copy down:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(ReferenceList,A1)),ReferenceList,""))

where ReferenceList is the name of the range housing the reference values black, blue, etc.<strike></strike>

 

[Thom_vee]

Do you mean Excel 2016? Unfortunately not. I`m on excel 2007.

Tried it, but geeting a

#NAME? error

<tbody>
</tbody>

 

[Aladin Akyurek]

Do you mean Excel 2016? Unfortunately not. I`m on excel 2007.

Tried it, but geeting a

#NAME? error

<tbody>
</tbody>

TEXTJOIN is not available on pre-2016 Excel versions. Hence the #NAME? error you get...

In E2 control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(IF(ISNUMBER(SEARCH(RefernceList,$A1)),", "&ReferenceList,"")),1,2,"")

For this formula to work, add the following <acronym title="visual basic for applications">VBA</acronym> code to your workbook as a module, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[Thom_vee]

Thanks you!! I got it working after a few bungling attempts. However, the formula seems to be case sensistive.
For eg: if my values cotain Blue instead of blue, it doesnt pick it up.

Can it be made case insensitive?

Thanks

TEXTJOIN is not available on pre-2016 Excel versions. Hence the #NAME? error you get...

In E2 control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(IF(ISNUMBER(SEARCH(RefernceList,$A1)),", "&ReferenceList,"")),1,2,"")

For this formula to work, add the following <acronym title="visual basic for applications">VBA</acronym> code to your workbook as a module, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[Thom_vee]

Sorry, I am wrong it isnt case sensistive, but it is failing on some rows. I assumed it was case sensitivity. Not sure why.
Also, it is adding multiple commas. Please see screenshot here

Thanks you!! I got it working after a few bungling attempts. However, the formula seems to be case sensistive.
For eg: if my values cotain Blue instead of blue, it doesnt pick it up.

Can it be made case insensitive?

Thanks

 

[Aladin Akyurek]

Sorry, I am wrong it isnt case sensistive, but it is failing on some rows. I assumed it was case sensitivity. Not sure why.
Also, it is adding multiple commas. Please see screenshot here

Let's sort the reference list in ascending order to see if you get more hits...

By the way, you need to confirm the formula with control+shift+enter, not just enter. Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key.

 

[Thom_vee]

Yes it is sorted from A- Z. I used Control+shift+enter too.
After applying the formula ang geeting results as in the screenshot earlier, if I add some of the missing values to the reference list at the bottom (duplicated), then it the missing values get populated (and values which were already present get duplicated too.)

Let's sort the reference list in ascending order to see if you get more hits...

By the way, you need to confirm the formula with control+shift+enter, not just enter. Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key.

 

[Aladin Akyurek]

Yes it is sorted from A- Z. I used Control+shift+enter too.
After applying the formula ang geeting results as in the screenshot earlier, if I add some of the missing values to the reference list at the bottom (duplicated), then it the missing values get populated (and values which were already present get duplicated too.)

I think we need the contents of the ReferenceList. Care to post that?

 

[Thom_vee]

I just discovered the problem. While dragging down, the reference list row numbers were getting incremented, so for rows down the sheet, reference values in the top of the referencelist were being omitted.
Thank you so much for your help.