Results 1 to 4 of 4

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

This is a discussion on Copying the first word from a cell and replacing another word from the same cell with it within the Excel Questions forums, part of the Question Forums category; Hello, I am trying to figure out a way to automatically copy the first word of a cell and then ...

  1. #1
    New Member
    Join Date
    Jul 2017
    Posts
    2

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

    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.

  2. #2
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    2,083

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

    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:

    AB
    1monkeys are ~monkeys are monkeys
    2apples are ~ but not all ~ are bad ~apples are apples but not all apples are bad apples

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=SUBSTITUTE($A1,"~",LEFT($A1,FIND(" ",$A1)-1))



    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - see here for some tools.
    Marvin: I've been communicating with the ship.
    Ford: What did it say?
    Marvin: It hates me.


  3. #3
    New Member
    Join Date
    Jul 2017
    Posts
    2

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

    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?

  4. #4
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    2,083

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

    You'd need to provide examples of where it doesn't work before I could comment on any potential fixes.

    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - see here for some tools.
    Marvin: I've been communicating with the ship.
    Ford: What did it say?
    Marvin: It hates me.


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com