How to get the specific charachters from the text?

[Dezom]

Hello Everyone,

I would like to take the specific characters from the texts in the first column into second column with the formula. Could you help me with this? Which formula should I use?

We have 5 specific characters ("Correction", "AB Correction", "Secondary Work", "Final Check", "Customer Request" in each line that consist of 1000 rows.

Protoype example like below;
First Column and second Column respectively should be like below;

fhdfhfdı Correction jdfsfhdsofddfdfdffd ==> Correction
dfdfnmcxzkn AB Correction jfjjA23 ==> AB Correction
fdjfsdsfh Secondary Work 934jpfhods ==> Secondary Work
"Final Check" bnnnnnnnndfsds ==> Final Check
341 Customer Request ==> Customer Request

Thanks in advance

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B
1
2	fhdfhfdı Correction jdfsfhdsofddfdfdffd	Correction
3	dfdfnmcxzkn AB Correction jfjjA23	AB Correction
4	fdjfsdsfh Secondary Work 934jpfhods	Secondary Work
5	"Final Check" bnnnnnnnndfsds	Final Check
6	341 Customer Request	Customer Request
Data

Cell Formulas
Range	Formula
B2:B6	B2	=LOOKUP(2,1/(ISNUMBER(SEARCH({"Correction","AB Correction","Secondary Work","Final Check","Customer Request"},A2))),{"Correction","AB Correction","Secondary Work","Final Check","Customer Request"})

 

[jmacleary]

Hello and welcome to the forum. Please have a look at using XL2BB (theres a link to it above the entry box) to present any future questions more clearly. I have produced an answer for you using it here:

Book1
	A	B
3	fhdfhfdı Correction jdfsfhdsofddfdfdffd	Correction
4	dfdfnmcxzkn AB Correction jfjjA23	AB Correction
5	fdjfsdsfh Secondary Work 934jpfhods	Secondary Work
6	Final Check bnnnnnnnndfsds	Final Check
7	341 Customer Request	Customer Request
8	bbdbdbb nndsr tntr	None found
Sheet1

Cell Formulas
Range	Formula
B3:B8	B3	=IF(ISNUMBER(FIND("AB Correction",A3)),"AB Correction",IF(ISNUMBER(FIND("Correction",A3)),"Correction",IF(ISNUMBER(FIND("Secondary Work",A3)),"Secondary Work",IF(ISNUMBER(FIND("Secondary Work",A3)),"Secondary Work",IF(ISNUMBER(FIND("Final Check",A3)),"Final Check",IF(ISNUMBER(FIND("Customer Request",A3)),"Customer Request","None found"))))))

 

[jmacleary]

Ah, fluff often has a better answer than me.

 

[Fluff]

Plenty of ways to skin a cat
And is one better than another, or just different?

 

[Dezom]

Hello Thanks alot to both of you. Both formula are working perfectly. I could never imagined a formula like first one. I will try to undertand it as well. But I would like to ask you jmacleary; In your formula there is one bug. If we put Correction to the first sequence and AB Correction to the second, then formula brings Correction as a result. In case I do not want to make this control manually, how would we develop your formula?

 

[jmacleary]

Thanks for the feedback and I'm pleased they work for you, but I'm not sure I understand your question. Both Fluff and myself have ordered the phrases with the more specific first, so they firstly look for the complete "AB Correction" and only if that is not found will look for "Correction" next. This will I think produce the result you want.

 

[Dezom]

Thanks for the feedback and I'm pleased they work for you, but I'm not sure I understand your question. Both Fluff and myself have ordered the phrases with the more specific first, so they firstly look for the complete "AB Correction" and only if that is not found will look for "Correction" next. This will I think produce the result you want.

What I am saying is illustrated below;

For example for the column;
fhdfhfdı AB Correction jdfsfhdsofddfdfdffd --> IF(ISNUMBER(FIND("Correction";A2));"Correction";IF(ISNUMBER(FIND("AB Correction";A2));"AB Correction"; ........................................)
Formula will resulted in giving Correction instead of AB Correction. So for the situations which have lots of intersected charachteres, this formula will be then not very effective.
I am just asking if there was a possibility to prevent this bug with small adjustments.

 

[Fluff]

Why did you change the formula? Both jmaclreay & i put the AB Correction first to make sure that it was picked up correctly.
If you want it to work, put it back into the correct order

 

[Dezom]

Hello Again, I have one question depend on that previous question.
There are variety of data in those forms below
A??-???? (A21-783A, A87-4GH1 etc.)
1??-???? (12Y-87GH etc)
F??-????
G??-????
C??-????

What I need to do is to get this text from the main text which covers this data (see below) I have written one formula with IFERROR, however it is working only for two variations like below.

Kitap1.xlsx
	A	B
15	Differential A21-9568 Autonomous	A21-9568
16	Model XL 12C-42A3 Vehicle	12C-42A3
17	A21-4444 Suspansion	A21-4444
18	Secondary Work A78-43H5	A78-43H5
19	Primary Work F44-3344 Som	#DEĞER!
20	Door Opening G22-4322 Work	#DEĞER!
Sayfa1

Cell Formulas
Range	Formula
B15:B20	B15	=IFERROR(MID(A15,SEARCH("A??-????",A15),8),MID(A15,SEARCH("1??-????",A15),8))