How to get the specific charachters from the text?

Dezom

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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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"})
 
Upvote 0
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"))))))
 
Upvote 0
Plenty of ways to skin a cat :)
And is one better than another, or just different?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 ;)
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top