+ Reply to Thread
Results 1 to 6 of 6

How to insert hyphen between words using VBA?

  1. #1
    Registered User
    Join Date
    05-28-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    18

    How to insert hyphen between words using VBA?

    Hi

    I have long list of categories in excel 2016 and I want to insert hyphen between words with a slash (/)

    I am just uploading samples with file name existed-categories and how-I-want

    Existed-categories means I already have a list and how-i-want means you will see read text.

    Thanks in advance.
    Attached Files Attached Files
    Last edited by hasibsafi; 01-15-2019 at 12:56 PM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: How to insert hyphen between words using VBA?

    Not saying I'll answer this as VBA is not my specialist subject but this may help anyone solving this.

    1. Inconsistencies - Why have you got a hyphen in D4 in the red "AB apple"? It doesn't exist in black "AB apple" in D4.
    Yet you've NOT put a hyphen in "Dry fruit" in red in C3 ???
    Yet B7 and C7 "Fresh fruit" you've put the hyphen in again.

    2. Will there only be 4 columns ("levels") or could there be more?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    05-28-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: How to insert hyphen between words using VBA?

    Thanks for the reply.

    1. Inconsistencies - Why have you got a hyphen in D4 in the red "AB apple"? It doesn't exist in black "AB apple" in D4.

    Ans. I already have list in black like that. But I want to insert slash and copy the words from first row to second row. first and second row
    words to the third row. And so on.

    Ops. My mistake. I missed hypen in c-3. Can I re-upload that?

    2. Will there only be 4 columns ("levels") or could there be more?

    Ans. There will from 2 colums to 6 colums.

    Kind regards

  4. #4
    Registered User
    Join Date
    05-28-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: How to insert hyphen between words using VBA?

    re-upload excel files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: How to insert hyphen between words using VBA?

    This is a formula based proposal.
    E2:H18 are populated using: =IF(A2<>"",A2,E1)
    I2:K18 are populated using: =SUBSTITUTE(F2," ","-")
    L2:N18 are populated using: =F2&"/"&E2&"-"&I2
    A20:A39 are populated using: =IF(A1="","",A1)
    B20:D39 are populated using: =IF(B1="","",L1)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: How to insert hyphen between words using VBA?

    Hello hasibsafi,

    I waited to see if anyone would answer you with a VBA solution. Since they have not, I will post my solution to your problem. This was very challenging because you are adding the relationship path to each member of an existing hierarchical list that has no objects. The attached workbook contains the code shown below. If you need help adapting this code to your data, let me know.

    Macro Code
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

+ 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. [SOLVED] Insert a hyphen after 6 digits
    By CA_needing_help in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-13-2016, 12:55 AM
  2. Insert hyphen/dash in the middle of numbers
    By noxious in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-08-2015, 10:19 PM
  3. [SOLVED] Insert Hyphen
    By mgs in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-12-2013, 02:08 AM
  4. insert a hyphen between alpha and numeric in the same cell
    By jspeterson in forum Excel General
    Replies: 4
    Last Post: 02-09-2012, 09:52 PM
  5. Is it possible to insert a hyphen in text by formula?
    By Chineseand in forum Excel General
    Replies: 2
    Last Post: 01-12-2012, 10:08 AM
  6. Is it possible to insert a hyphen in text by formula?
    By Brahma72472 in forum Excel General
    Replies: 5
    Last Post: 01-11-2012, 09:55 AM
  7. Insert Hyphen (-) between two words
    By rvc81 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2010, 05:15 AM

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