+ Reply to Thread
Results 1 to 15 of 15

Capturing right characters in variable string

  1. #1
    Registered User
    Join Date
    10-20-2016
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    7

    Capturing right characters in variable string

    Have a spreadsheet that i want to sub out the right four characters from a column that is variable in length:

    Column C, row 3 to a variable that I can calculate, ie. C3 to lrow. Column has an eight digit number and I want to remove the first four (same on all numbers).

    All assistance appreciated.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Capturing right characters in variable string

    Place this formula in cell D3 and copy it down as far as you have data: =Right(C3,4)
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Capturing right characters in variable string

    If they are numbers, you could use =MOD(C3, 10000)
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    10-20-2016
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    7

    Re: Capturing right characters in variable string

    Both would work, but looking to do it in the macro, in a loop, i.e.


    for rw = 3 to lrow
    rw, 3 = "calculation"

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Capturing right characters in variable string

    Maybe:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-20-2016
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    7

    Re: Capturing right characters in variable string

    JOHN H. DAVIS - Many THX. . .
    Little modification to, put it in "R" (all used through "Q") then copy "R" and paste \ values back into C and solves the problem

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Capturing right characters in variable string

    Not sure I understand. See if this helps.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    10-20-2016
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    7

    Re: Capturing right characters in variable string

    Sorry, your initial solution worked fine. Just put it in "R" vs. "D" and then copied back to "C" (deleted "R") all in the macro.

    Working great (that's why you get the big bucks)

  9. #9
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Capturing right characters in variable string

    Much easier method.
    Avoid worksheet interaction as much as possible, make use of excel memory to do the calculations and write results back in 1 go. (that's where the big bucks are)

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  10. #10
    Registered User
    Join Date
    10-20-2016
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    7

    Re: Capturing right characters in variable string

    OK, you both make the big bucks. I don't do this very often, anymore, and my 70+ brain cells are degenerating so I defer to both of you.
    Off today, but will make the changes this afternoon.
    Just a note on why I am doing this - we have a column that contains an 8-digit delivery number. The team members picking the orders have to verify which delivery the items go with and one of our Ops Mgrs suggested that, since the first four were the same, maybe remove them to make it a 4-digit number, ergo easier to read. New process going in on Halloween.

  11. #11
    Registered User
    Join Date
    10-20-2016
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    7

    Re: Capturing right characters in variable string

    Many THX (to both of you). Latest response works great. Only change I made was to the "sn = Range("C3:C" & Cells(Rows.Count, 3).End(xlUp).Row)" line. Since I already know the last row, which is not actually the last row on the sheet, but the last row with delivery numbers in it, I substituted it in the instruction, i.e. "sn = Range("C3:C" & lrow4)" and works GREAT.

    Again, many THX to both.

  12. #12
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Capturing right characters in variable string

    You're more than welcome.

  13. #13
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Capturing right characters in variable string

    @ BWhipple44 - Ditto post 12.

    @ bakerman2 - Thanks for the tips.

  14. #14
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Capturing right characters in variable string

    And just in case you would want the result in the column to the right!!!
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    10-20-2016
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    7

    Re: Capturing right characters in variable string

    THX. . . Want to keep them in the same column - actually use all the columns through "Q".

    And total at a variable row (~30-50 depending on day of the week), then add ten rows of information below the total row - some numeric and some alpha which is why I had to calculate the last row of "real" data.

+ 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. Replies: 7
    Last Post: 12-17-2015, 05:14 PM
  2. [SOLVED] How can I assign the last 4 characters of a string to a string variable?
    By dschmitt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-29-2015, 01:41 AM
  3. Replies: 4
    Last Post: 06-04-2014, 04:12 PM
  4. [SOLVED] Capturing open workbook as variable?
    By aaron.irvine in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-11-2013, 04:34 AM
  5. [SOLVED] Special characters in a string variable
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2006, 03:40 PM
  6. [SOLVED] How do I find a specific characters position in a string variable
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2006, 10:30 AM
  7. Pass multiple characters to a string variable
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-19-2005, 06:26 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