split and place specific word only

kayza

Board Regular
Joined
Apr 29, 2015
Messages
56
Office Version
  1. 2007
Platform
  1. Windows
I have a sentence in cell A1 that contains a few words, but I just want to take a specific word and put it in another cell, for example B1 and so on


the sentence in cell A1 is as follows:
Name: one; Class: A; Add: yes; Name: two; Class: B; Add: no; Name: three; Class: A; Add: no


the results I want to display in cells B1 to B3 are as follows
Add: yes
Add: no
Add: no


What is the right formula to solve this problem


Thank you in advance
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
Hi,

Try this and drag down :

B1=MID($A$1,FIND("*",SUBSTITUTE($A$1,"Add:","*",ROW(A1))),8)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,499
Office Version
  1. 2016
Platform
  1. Windows
Hi,

If what you want extracted is Actually either "Add: yes" or "Add: no" without quotes, use this,
if the text string can be variable after "Add:" (i.e. Add: undetermined, Add: undecided, Add: unknown, etc.), let me know, I'll modify formula to suit:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name: one; Class: A; Add: yes; Name: two; Class: B; Add: no; Name: three; Class: A; Add: no</td><td style=";">Add: yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Add: no</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">Add: no</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet360</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=IFERROR(<font color="Blue">TRIM(<font color="Red">MID(<font color="Green">SUBSTITUTE(<font color="Purple">A$1,";"," "</font>),FIND(<font color="Purple">"|",SUBSTITUTE(<font color="Teal">A$1,"Add:","|",ROW(<font color="#FF00FF">B1</font>)</font>)</font>),8</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

Formula copied down.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

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
Top