+ Reply to Thread
Results 1 to 11 of 11

Stripping text from urls with numbers/text/hyphens without effecting cells.

  1. #1
    Registered User
    Join Date
    03-03-2009
    Location
    Gainesville, FL
    MS-Off Ver
    Excel 2002 SP3
    Posts
    39

    Stripping text from urls with numbers/text/hyphens without effecting cells.

    Hi,

    My problem is that when I generated my urls to online photos, I referenced cells where some are only numerical (ex. 479) while others contain a numerical/text mix (ex. 3014-RACK). Here is my url code in excel...

    Please Login or Register  to view this content.
    And here is the resulting links, one numerical, one mixed...

    http://www.website.com/registry_imag...L-CLAD_479.jpg

    http://www.website.com/registry_imag..._3014-RACK.jpg

    Is there a way to automatically remove the text "-RACK" from the generated url without removing it from the linked cell D4 containing "3014-RACK"?

    Oh. Just to clarify if needed.
    C4 = ALL-CLAD
    G4 = STAINLESS
    D4 = 3014-RACK

    Thanks in advance,

    Chris
    Last edited by giallofever; 03-07-2009 at 05:17 PM. Reason: Mistyped Title / Provided more info for clarity.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    You don't show us the formula creating that URL, so you'll have to interpret this, but you can use the SUBSTITUTE function to strip out specific text, like so:

    =SUBSTITUTE(FormulaThatCreatesUrlHere,"-RACK","")

    ...more simply to understand:

    =SUBSTITUTE(A1,"-RACK","")
    _________________
    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!)

  3. #3
    Registered User
    Join Date
    03-03-2009
    Location
    Gainesville, FL
    MS-Off Ver
    Excel 2002 SP3
    Posts
    39

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    Thanks for the quick reply. And thank you for the substitute code. That is one step closer to what I need. The problem is that there are a variety of texts such as 4302-RACK, 2345-O, 4567RTS, etc. I am looking for a way to automatically remove the text and hyphens regardless of the actual text. I did include the code generating the url in my previous post, at least that is what I think you are looking for. Here it is again.

    =HYPERLINK("http://www.website.com/registry_images/" & (C4) & "/" & (G4) & "/" & (C4) & "_" & (D4) & ".jpg")

    And in addition to the text and hyphens, some have decimals 1234.1
    I would also like those removed as well, if possible.

    Thanks again for your help.

    Chris
    Last edited by giallofever; 03-05-2009 at 02:41 PM. Reason: Added info for clarification

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    Wow, lots of issues, my friend. Well, you'll have to "build" a solution that solves each problem. So far the needs are
    a) remove all values from the decimal and on from a cell value
    b) strip out any non-numeric values from the cell too

    Ok, a simple LEFT() formula takes all the value before a decimal:
    =LEFT(D4,FIND(".",D4)-1)

    But that returns an error if there is no decimal, so we have to test for that, too:
    =IF(ISERROR(SEARCH(".",D4)),D4,LEFT(D4,FIND(".",D4)-1))

    Now, you need a function to search the remaining value from that formula and strip out all the non-numerics. I use a UDF for that called CleanAll. Here's the code for the new function:
    Please Login or Register  to view this content.
    This UDF is pretty flexible, you can see some another option commented out in the code that would change the function to stripping out numbers...worth playing with in your free time.

    Anyway, add that code into a standard module (same place as regular macros) and your new function CleanAll() is ready to use.

    The final formula to insert into the last one above would look like this:
    =IF(ISERROR(SEARCH(".",D4)),CleanAll(D4),CleanAll(LEFT(D4,FIND(".",D4)-1)))

    Now, all of that inserted into your URL formula (ugh) would look like:

    =HYPERLINK("http://www.website.com/registry_images/" & (C4) & "/" & (G4) & "/" & (C4) & "_" & (IF(ISERROR(SEARCH(".",D4)),CleanAll(D4),CleanAll(LEFT(D4,FIND(".",D4)-1)))) & ".jpg")

    You might have to play with that a little. Sample book attached showing explanation.

  5. #5
    Registered User
    Join Date
    03-03-2009
    Location
    Gainesville, FL
    MS-Off Ver
    Excel 2002 SP3
    Posts
    39

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    WOW! I never knew Excel could be so... involving. I can't even begin to thank you enough. This is exactly what I needed, but I will need to play around with it a bit. It actually looks like a lot of fun and I might start dabbling in VBA. BTW, I didn't see the attached file you mentioned.

    After this, only one more problem, but one issue at a time.


    Again, thank you very much.

    Chris

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    Sorry, here you go...

    If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-03-2009
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    there's a mid() function too to get just part of a string.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    I suspect you can do this without need for VBA, does the below work for you ?

    =HYPERLINK("http://www.website.com/registry_images/" & C4 & "/" & G4 & "/" & C4 & "_" & INT(LOOKUP(9.99E+307,--MID(D4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},D4&1234567890)),ROW(INDIRECT("1:"&LEN(D4)))))) & ".jpg")

    Assumes you will always have a number within the string present in D4.

  9. #9
    Registered User
    Join Date
    03-03-2009
    Location
    Gainesville, FL
    MS-Off Ver
    Excel 2002 SP3
    Posts
    39

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    Thanks for the code JBeaucaire, I have been playing around with it and I keep getting a #NAME error. I'll keep testing to get it to work so I can practice learning VBA code. (Thanks for the inspiration.) But I am interested in in getting your code to work. Any thoughts or input about the #NAME error I am getting?

    In the meantime, I am going to use the code posted by DonkeyOte as it takes care of me getting this project takin' care of right away.

    Thanks guys for all your help, I now know that there are many ways to solve problems, both equally effective.

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    #NAME usually just means a typo in the function title, either in the original code or in your implementation in a cell.

    Post up your book if you can't spot the typo, we'll help.
    Last edited by JBeaucaire; 03-07-2009 at 08:27 PM.

  11. #11
    Registered User
    Join Date
    03-03-2009
    Location
    Gainesville, FL
    MS-Off Ver
    Excel 2002 SP3
    Posts
    39

    Re: Stripping text from urls with numbers/text/hyphens without effecting cells.

    Thanks, I'll post it Monday when I get to work if I can't fix it.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1