none
Excel vba Split text and insert hyphen RRS feed

  • Question

  • Hi,

    I have a requirement s to remove char from the left and at the same time insert hyphen on the string or text. Once i have the result i wanted to check from the other open workbook if those text is found or exist the get the category for a particular SKU.

    Thank you in advance.

    below is a sample data.

    ------string to be split-----Result
    TALK3000430iPH5S32SIL----300-0430-iPH5S32SIL
    MP3000440iPH5C16BLU------300-0440-iPH5C16BLU
    TALK3003110REVERE3-------300-3110-REVERE3
    TALK3003110LUCID3--------300-3110-LUCID3
    MP3000460iPH5S32GLD------300-0460-iPH5S32GLD
    TALK3004410ONERMX--------300-4410-ONERMX

    Lookup table as reference to check if the result from the split text is found or exist. This is located from other open workbook.

    -------Split string----Type------Category
    300-0430-iPH5S32SIL--- repair--    Apple
    300-0440-iPH5C16BLU--- repair---   Apple
    300-3110-REVERE3------ test------  Orange
    300-3110-LUCID3------- test------  Orange
    300-4410-ONERMX------- test------  Manggo

    Desired Result

    Copy to my active worksheet

    ---Label----------Category----Model
    Apple----300-0430-iPH5S32SIL--iPH5S32SIL  
    Apple----300-0440-iPH5C16BLU--iPH5C16BLU
    Orange-- 300-3110-REVERE3-----REVERE3
    Orange---300-3110-LUCID3------LUCID3
    Manggo---300-4410-ONERMX------ONERMX


    • Edited by Lenoj Wednesday, September 10, 2014 3:10 AM
    Wednesday, September 10, 2014 3:09 AM

Answers

  • For the first part of your question you should be able to adapt the macro I gave you in your recent related thread.

    Find text in a string

    Check each character until you find the first number, then the rest should be straightforward, build the string with the 3 numbers, 4 numbers and  add remaining text, padded with dashes. It is not possible to guess the logic your second caption and in turn of the other parts of your question. 

    • Marked as answer by Lenoj Thursday, September 11, 2014 1:25 AM
    Wednesday, September 10, 2014 9:35 AM
    Moderator

All replies

  • For the first part of your question you should be able to adapt the macro I gave you in your recent related thread.

    Find text in a string

    Check each character until you find the first number, then the rest should be straightforward, build the string with the 3 numbers, 4 numbers and  add remaining text, padded with dashes. It is not possible to guess the logic your second caption and in turn of the other parts of your question. 

    • Marked as answer by Lenoj Thursday, September 11, 2014 1:25 AM
    Wednesday, September 10, 2014 9:35 AM
    Moderator
  • Hi Peter, Thank you very much. Its working already. (Solved).

    Thursday, September 11, 2014 1:25 AM