+ Reply to Thread
Results 1 to 7 of 7

Substrings from String

  1. 02-19-2010, 02:10 PM #1
    bdb1974 is offline
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Austin
    Posts
    660

    Substrings from String

    Hi all,

    I'm trying to break down a string and put parts into designated cells using the following
    code. I'm not sure what's causing the problem, but the substring results are not correct.

    Please Login or Register  to view this content.
    Attached also is an example booklet. I've given an example of a 2nd string possiblity
    that I'd like to have broken down if possible.

    Any help getting this working is appreciated.

    Thanks

    BDB
    Attached Files Attached Files
    Last edited by bdb1974; 02-23-2010 at 05:08 PM.
    Reply With QuoteRegister To Reply
  2. 02-19-2010, 04:05 PM #2
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Substrings from String

    Based on your sheeet, the following worksheet formulas give you the results you want. ALL four formulas have to be there for the second one to work, it is dependent on formulas 1 and 3.

    In C6: =IF(ISNUMBER(SEARCH("4WW", $C$3)), "4WW", IF(ISNUMBER(SEARCH("4PY", $C$3)), "4PY", "neither"))
    In C7: =MID($C$3,SEARCH($C$6,$C$3)+LEN($C$6)+1,SEARCH($C$8,$C$3)-(SEARCH($C$6,$C$3)+LEN($C$6)+1))
    In C8: =IF(ISNUMBER(SEARCH("UG Feeder", $C$3)), "UG Feeder", "UG")
    In C9: =MID($C$3,SEARCH("|",SUBSTITUTE($C$3,"\","|",LEN($C$3)-LEN(SUBSTITUTE($C$3,"\",""))))+1,99)

    Maybe that will help you design the macro.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 02-19-2010, 04:52 PM #3
    bdb1974 is offline
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Austin
    Posts
    660

    Re: Substrings from String

    Thanks JBeaucaire for your input. I'm just wondering how within the formuals ,I'm going to sub in a variant for the Search fields? I tried subsituting one of the slant bars,"\" in the string to a "~" as a reference point /marker. Then I was hoping to transfer only the section of the string that was on the side of the marker that I was needing for the designated cell. :/
    Reply With QuoteRegister To Reply
  4. 02-19-2010, 05:00 PM #4
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Substrings from String

    I did that with the 4th formula, I just used the pipe character instead of tilde. Tilde has meaning in VBA such that I don't use it.
    Reply With QuoteRegister To Reply
  5. 02-19-2010, 05:04 PM #5
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Substrings from String

    Simplest would be to put the formulas in the cells and leave them there. As soon as you enter a new value in C3, you have your values and can copy them elsewhere.
    Reply With QuoteRegister To Reply
  6. 02-20-2010, 11:58 PM #6
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Substrings from String

    Hello bdb1974,

    Here is a macro to split up the string. Since string manipulation in VBA and Excel is limited to simple operations, this macro uses VB Script's Regular Expressions to perform pattern matching, string extraction, and replacement. I know this looks like gibberish, but is a very powerful and flexible way to manipulate strings. For anyone who is serious about programming or working web pages, this is a must have tool. This macro has been added to the attached workbook.
    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 With QuoteRegister To Reply
  7. 02-23-2010, 05:08 PM #7
    bdb1974 is offline
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Austin
    Posts
    660

    Re: Substrings from String

    Thanks Leith,

    Here's another method that I finally came up with and used after playing around with some code.

    Please Login or Register  to view this content.
    Thanks for everyone's help.

    BDB
    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)

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