Copy & remove text before & after specific character

mljohn

Board Regular
Joined
Aug 26, 2004
Messages
196
Office Version
  1. 365
Platform
  1. Windows
How do I move to another column, all text from a cell that follows a specific character, such as ":"?

Also, How do I move to another column, all text from a cell that precedes a specific character, such as ">"?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Thanks.

How do I at the same time remove from the original cell what was moved to the other column?
 
Upvote 0
Fantastic.

How would I move text to another column by finding the word "yield" and not only moving the word "yield" but several characters before it?

Such as "20,000 yield" or "(5,000 yield)" or "1500 yield".
The amounts change but not the word.

I don't want the parenthesis just for example "5,000 yield".
 
Upvote 0
Maybe

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"yield",""),"(",""),")","")+0
 
Upvote 0
Drum (25 000 Yield)
Drum (30 000 Yield)
Drum (25 000 Yield)
Drum (40 000 Yield)
Drum (Yield 30 000)
Black Toner 10 000 Yield
Cyan Toner 6 600 Yield
Magenta Toner 6 600 Yield
Yellow Toner 6 600 Yield
 
Upvote 0
Very precious commands.

How to do B2 if there is more than 2 words and multiple ":". For example Fred:Scuttle:Paris and I want to have only the last word? Right now it would give "Scuttle:Paris"

Thanks


Try like this

Sheet14

*AB
1Fred:ScuttleScuttle
2Fred>ScuttleFred

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:131px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=RIGHT(A1,LEN(A1)-FIND(":",A1))
B2=LEFT(A2,FIND(">",A2)-1)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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