+ Reply to Thread
Results 1 to 7 of 7

concatenate - join multiple words with a special character

  1. 06-26-2023, 06:46 AM #1
    codeBeast
    codeBeast is offline
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    concatenate - join multiple words with a special character

    Hi. I have multi word phrases like phrasal verbs eg: take on, take advantage of...
    They cause problems when I use them in mysql queries. I need to concatenate a character between the words eg: take&on, take&advantage&of. Then copy and paste that formula to the other columns and then down to the end.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-26-2023, 06:54 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,757

    Re: concatenate - join multiple words with a special character

    Do you mean this?

    =SUBSTITUTE(A2," ","&")
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 06-26-2023, 07:13 AM #3
    codeBeast
    codeBeast is offline
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    Re: concatenate - join multiple words with a special character

    =SUBSTITUTE(A2," ","&")

    yes, I think that's it. Could you possibly pop it in the excel please so I can download it. On my macbook pro - excel 365 - nothing ever works when I cut and paste in the formulas - only the excels that I download from here - don0't ask me why.
    Thank you.
    Reply With QuoteRegister To Reply
  4. 06-26-2023, 07:14 AM #4
    codeBeast
    codeBeast is offline
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    Re: concatenate - join multiple words with a special character

    I get this error


    Not trying to type a formula?
    When the first character is an equal ("=") or minus ("-") sign, Excel thinks it's a formula:

    • you type: =1+1, cell shows: 2

    To get around this, type an apostrophe ( ' ) first:

    • you type: '=1+1, cell shows: =1+1

    I try the apostrophe and it doesn't work.
    Reply With QuoteRegister To Reply
  5. 06-26-2023, 07:19 AM #5
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: concatenate - join multiple words with a special character

    you mean something like this?
    option 1 option 2 option 3 option 4
    beonabout anacidtest cleanupyouract actthefool
    Absencemakestheheartgrowfonder cleanupyouract actthefool actyourage
    bebrought/calledtoaccount actthefool actyourage abalancingact
    There'snoaccountingfortaste(s)! actyourage abalancingact beahard/toughacttofollow
    anacidtest abalancingact beahard/toughacttofollow catchsbintheact
    cleanupyouract beahard/toughacttofollow catchsbintheact getinontheact
    actthefool catchsbintheact getinontheact getyouracttogether
    actyourage getinontheact getyouracttogether putonanact


    or

    option 1 option 2 option 3 option 4
    be&on&about an&acid&test clean&up&your&act act&the&fool
    Absence&makes&the&heart&grow&fonder clean&up&your&act act&the&fool act&your&age
    be&brought/called&to&account act&the&fool act&your&age a&balancing&act
    There's&no&accounting&for&taste(s)! act&your&age a&balancing&act be&a&hard/tough&act&to&follow
    an&acid&test a&balancing&act be&a&hard/tough&act&to&follow catch&sb&in&the&act
    clean&up&your&act be&a&hard/tough&act&to&follow catch&sb&in&the&act get&in&on&the&act
    act&the&fool catch&sb&in&the&act get&in&on&the&act get&your&act&together
    act&your&age get&in&on&the&act get&your&act&together put&on&an&act


    anyway maybe Power Query will work on your Mac
    Attached Files Attached Files
    Last edited by sandy666; 06-26-2023 at 07:25 AM.
    Reply With QuoteRegister To Reply
  6. 06-26-2023, 07:37 AM #6
    HansDouwe
    HansDouwe is offline
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: concatenate - join multiple words with a special character

    Please replace in the formula of Ali (POST #2) , by ; :
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 06-26-2023, 12:41 PM #7
    HansDouwe
    HansDouwe is offline
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: concatenate - join multiple words with a special character

    Thanks for the rep .
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding a Special character before specific word or words.
    By chakkaravarthy.m in forum Excel General
    Replies: 10
    Last Post: 07-30-2018, 04:59 AM
  2. Adding a special character in front of a word or words at a time
    By chakkaravarthy.m in forum Excel General
    Replies: 3
    Last Post: 07-30-2018, 03:52 AM
  3. Adding a special character in front of a particualrs words
    By chakkaravarthy.m in forum Excel General
    Replies: 3
    Last Post: 06-19-2016, 06:12 AM
  4. [SOLVED] Change last character(s) in multiple words in a cell
    By frankt68 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-02-2014, 05:48 AM
  5. Adding Multiple Cells Text with Special Character
    By sunnyujjawal in forum Excel General
    Replies: 8
    Last Post: 09-03-2013, 09:25 AM
  6. Need to highlight special character and the cells contains special character
    By dhilipan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2013, 01:36 AM
  7. to find and replace the special character in multiple worksheet in a workbook
    By Vinutha in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-11-2012, 11:16 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1