[Solved] Find and copy substrings in cells

[jivana13]

Hi all, I have spent quite a few hours on this problem and researching many similar questions on the net, but have not come close to a solution. I hope that someone can help.
I have a large sheet with items and their attributes in rows, and one column containing the item's colour, but also also a bunch of other information in no particular order. All I want is the colour to be copied and put by itself into another cell in the same row. The cells area already there.
I didn't think this would be so hard
The good thing is that the colours are actually from a set range of about 20, for which I have the exact spellings in a little table.
I tried all the lookup functions, with Search and Find etc etc but somehow it didn't work for me
So in this file, there is a small sample of what I am trying to do:

Find and copy string in cell to other cell.ods

(14.48 KiB) Downloaded 144 times

Thank you for any help you can give.

I am not a beginner, however somewhat rusty on more complex functions

[Zizi64]

What about the usage of the function FIND() in a nested IF() function?

[Zizi64]

Or you can Write your own FIND function:

Code: Select all

REM  *****  BASIC  *****
Option Explicit

Function MyFind(aTextToFind as variant, ActualString as string) as String
 Dim i as integer
 Dim ActualToFind as string
 
	If NOT IsArray(aTextToFind) Then
		MyFind = "The 'aTextToFind' parameter is not an Array"
		Exit function
	else
		For i = LBound(aTextToFind, 1) To UBound(aTextToFind, 1)
		ActualToFind = aTextToFind(i, 1)	
			If InStr(1,  ActualString, ActualToFind, 1) <> 0 then
				MyFind = ActualToFind
				Exit Function
			end if
		next i
	End if
 MyFind = "Not found"
end function

Note:
the "Black" and the "Black (kinda gray)" requests a more tricky finding method. It is simplier to rename the color. Or you can use a tricky storing method: move the longer name (what contain the color name "Black") before the shorter one.

MyFind_macro.ods

(15.31 KiB) Downloaded 141 times

[jivana13]

Wow, that sounds way more involved than I anticipated, so the order of the colours in the table matters as well. Thank you for you suggestion, not sure I will be able to.put it 8nto practice, but I will give it a try tomorrow

[Zizi64]

so the order of the colours in the table matters as well.

Of course the word "Black" will be found in the string "Black (kinda gray) but solid" and in the string "Black may be another colour" too.
My function returns with the first value what will be found. But you can modify and improve the code.

[MrProgrammer]

Hi, and welcome to the forum.

I want is the colour to be copied and put by itself into another cell in the same row

202401221011.ods

(9.51 KiB) Downloaded 142 times

In this example, the list of colors is A, B, C, D, E. The color for the row is placed in column G.

Saying the color (using a definite article) suggests to me that exactly one color is present, so:
• If no color is count, #N/A is returned.
• If more than one color is present, the leftmost one on the Colors sheet is selected so you need to put Black (kinda gray) before Black on that sheet.

I am not a beginner …

Hint: use the UPPER function if you need to do case insensitive comparisons.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[jivana13]

Thank you very much for your help @Zizi64 and @MrProgrammer, great stuff!

I simplified the colours by removing secondary Blacks etc and moved them in the right order to reflect the priorities and have implemented MrProgrammer's solution for now

I also like the idea of a function, however that will be for another day for me to study