Copying the first word from a cell and replacing another word from the same cell with it

Spasoval

New Member
Joined
Jul 17, 2017
Messages
2
Hello,

I am trying to figure out a way to automatically copy the first word of a cell and then replace a certain character (~ or -) in the same cell with that word. Also, I would like this to be done automatically in all cells from that sheet. Would this be possible to achieve with only excel?

Thanks and best regards!

P. S. If my explanation is a bit blurry, I will try to give a picture with an example of what I'm trying to do.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
If you want to put it back in the same cell then you need VBA. If you're happy to use another cell then you could use a formula:


Book1
AB
1monkeys are ~monkeys are monkeys
2apples are ~ but not all ~ are bad ~apples are apples but not all apples are bad apples
Sheet1
Cell Formulas
RangeFormula
B1=SUBSTITUTE($A1,"~",LEFT($A1,FIND(" ",$A1)-1))


WBD
 
Upvote 0
It looks like this formula is exactly what I need, but it gives me some errors, when I try to put it into my document and respectively does not work like in your example. Would I need to correct something? Any idea?
 
Upvote 0
You'd need to provide examples of where it doesn't work before I could comment on any potential fixes.

WBD
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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