How to get the specific charachters from the text?

Dezom

New Member
Joined
Jul 21, 2020
Messages
17
Office Version
2016
Platform
Windows, Mobile
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,505
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
+Fluff New.xlsm
AB
1
2fhdfhfdı Correction jdfsfhdsofddfdfdffdCorrection
3dfdfnmcxzkn AB Correction jfjjA23AB Correction
4fdjfsdsfh Secondary Work 934jpfhodsSecondary Work
5"Final Check" bnnnnnnnndfsdsFinal Check
6341 Customer RequestCustomer Request
Data
Cell Formulas
RangeFormula
B2:B6B2=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

Well-known Member
Joined
Oct 5, 2015
Messages
1,013
Office Version
365, 2007
Platform
Windows
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
AB
3fhdfhfdı Correction jdfsfhdsofddfdfdffd Correction
4dfdfnmcxzkn AB Correction jfjjA23 AB Correction
5fdjfsdsfh Secondary Work 934jpfhods Secondary Work
6Final Check bnnnnnnnndfsds Final Check
7341 Customer Request Customer Request
8bbdbdbb nndsr tntrNone found
Sheet1
Cell Formulas
RangeFormula
B3:B8B3=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

Well-known Member
Joined
Oct 5, 2015
Messages
1,013
Office Version
365, 2007
Platform
Windows
Ah, fluff often has a better answer than me.:):)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,505
Office Version
365
Platform
Windows
Plenty of ways to skin a cat :)
And is one better than another, or just different?
 

Dezom

New Member
Joined
Jul 21, 2020
Messages
17
Office Version
2016
Platform
Windows, Mobile
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

Well-known Member
Joined
Oct 5, 2015
Messages
1,013
Office Version
365, 2007
Platform
Windows
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

New Member
Joined
Jul 21, 2020
Messages
17
Office Version
2016
Platform
Windows, Mobile
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,505
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 21, 2020
Messages
17
Office Version
2016
Platform
Windows, Mobile
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
AB
15Differential A21-9568 AutonomousA21-9568
16Model XL 12C-42A3 Vehicle12C-42A3
17A21-4444 SuspansionA21-4444
18Secondary Work A78-43H5 A78-43H5
19Primary Work F44-3344 Som#DEĞER!
20Door Opening G22-4322 Work#DEĞER!
Sayfa1
Cell Formulas
RangeFormula
B15:B20B15=IFERROR(MID(A15,SEARCH("A??-????",A15),8),MID(A15,SEARCH("1??-????",A15),8))
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,930
Messages
5,508,176
Members
408,669
Latest member
AgsikapAko

This Week's Hot Topics

Top