+ Reply to Thread
Results 1 to 7 of 7

Formula to MERGE CELLS and SEPARATE WORDS with " - "

Hybrid View

Sarah1986 Formula to MERGE CELLS and... 08-21-2013, 10:33 AM
XOR LX Re: Formula to MERGE CELLS... 08-21-2013, 10:38 AM
L-Drr Re: Formula to MERGE CELLS... 08-21-2013, 10:40 AM
Sarah1986 Re: Formula to MERGE CELLS... 08-21-2013, 10:52 AM
XOR LX Re: Formula to MERGE CELLS... 08-21-2013, 11:06 AM
L-Drr Re: Formula to MERGE CELLS... 08-21-2013, 11:11 AM
Sarah1986 Re: Formula to MERGE CELLS... 08-21-2013, 11:23 AM
  1. #1
    Registered User
    Join Date
    12-27-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Formula to MERGE CELLS and SEPARATE WORDS with " - "

    Hi,

    I have this problem:

    I need to MERGE the textual (or numeric) content contained in 5 SEPARETED CELLS, INTO 1 CELL,
    and to SEPARATE ALL THE WORDS (OR NUMBERS), contained in that cell, WITH "-".

    Are you guys aware of a combination of formulas to make it?

    I thought of using "merge cells" and then "find space" and "substitute with - ",
    but is there something better?

    Many thanks

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Formula to MERGE CELLS and SEPARATE WORDS with " - "

    Hi,

    Something like:

    =A1&"-"&A2&"-"&A3&"-"&A4&"-"&A5

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Formula to MERGE CELLS and SEPARATE WORDS with " - "

    And if the old cells may contain more than 1 word each, build a SUBSTITUTE function around XORLX's formula
    When I say semicolon, u say comma!

  4. #4
    Registered User
    Join Date
    12-27-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula to MERGE CELLS and SEPARATE WORDS with " - "

    Thank you XOR LX, that formula is perfectly working if the cells have 1 value.

    Thank you L-Drr, I am trying to use the SUBSTITUTE formula on the A1 cell, that contains more than 1 word, for example: "Hi my name is"

    =SUBSTITUTE(A1;" ";"-";1)

    But in this way it puts the "-" just in the first position.

    How can I tell Excel to put the "-" among each word?

    many thanks

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Formula to MERGE CELLS and SEPARATE WORDS with " - "

    Just remove the last criterion:

    =SUBSTITUTE(A1;" ";"-")

    Regards

  6. #6
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Formula to MERGE CELLS and SEPARATE WORDS with " - "

    And then replace the A1 in post #5 with the formula in post #2, to get it all in one formula

  7. #7
    Registered User
    Join Date
    12-27-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula to MERGE CELLS and SEPARATE WORDS with " - "

    Many thanks for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. if cells contain "words" refer to other worksheet and replace the words
    By adehond in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 11:22 AM
  2. Replies: 6
    Last Post: 01-26-2012, 10:02 PM
  3. using "wrap text" and "merge cells" together
    By jerimd in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-06-2009, 03:50 PM
  4. Remove "TRUE" "FALSE" words from a linked checkbox
    By MDCK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-15-2008, 03:26 AM
  5. [SOLVED] Why "center across selection" rather than "merge" cells? What is.
    By Michele in forum Excel General
    Replies: 4
    Last Post: 04-25-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1