+ Reply to Thread
Results 1 to 17 of 17

Removing varied number of letters from end of number-letter string

  1. #1
    Registered User
    Join Date
    04-15-2015
    Location
    Bristol, England
    MS-Off Ver
    2013
    Posts
    4

    Lightbulb Removing varied number of letters from end of number-letter string

    I have hundreds of lines of data and need to consolidate the product numbers into generic product families.

    A few examples:

    RWH39585T339HBZ
    RWH39585T339GDT
    RWH39585T339R
    RWH39585T339RTT

    QW1410NHE
    QW1410TRR
    QW1410TB
    QW1410X

    F287387WW29384XT
    F287387WW29384TOP
    F287387WW29384R

    On the third example, there are also letters between two number strings - these need to stay intact, as do the letters at the start of the chain.

    I considered using the IF function to test if the last character on the right was a number or letter, and if it was a number then remove everything to the right of it by Substitution or using LEFT, but I'm not sure if this is possible to extract in this way.

    Any help would be much appreciated.

    Thanks, William

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Removing varied number of letters from end of number-letter string

    Hi,

    Is there a way we can store the generic product families on a different sheet before hand?
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    04-15-2015
    Location
    Bristol, England
    MS-Off Ver
    2013
    Posts
    4

    Re: Removing varied number of letters from end of number-letter string

    Thanks for the response. Preferably - No, this isn't possible.

    There is a possibility but this is also time consuming - I presume the next phase of your solution would be to perform an Index/Match or VLookup?

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Removing varied number of letters from end of number-letter string

    Then is there a possibility of knowing the minimum length of a Product Family?

    Also is there going to be a blank row after each product family as shown above?

  5. #5
    Registered User
    Join Date
    04-15-2015
    Location
    Bristol, England
    MS-Off Ver
    2013
    Posts
    4

    Re: Removing varied number of letters from end of number-letter string

    No, there is no space, but there is a way to know the minimum length of a product family and build from there.

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Removing varied number of letters from end of number-letter string

    Can you guide me how?

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,685

    Re: Removing varied number of letters from end of number-letter string

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    Please Login or Register  to view this content.
    A
    B
    1
    RWH39585T339HBZ RWH39585T339


    B
    1
    =RemoveAlphaInTheEnd(A1)


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  8. #8
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Removing varied number of letters from end of number-letter string

    @ Sixthsense

    That is insane..

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,685

    Re: Removing varied number of letters from end of number-letter string

    Quote Originally Posted by NeedForExcel View Post
    That is insane..
    Thanks for the rep and comment

  10. #10
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Removing varied number of letters from end of number-letter string

    Quote Originally Posted by :) Sixthsense :) View Post
    Thanks for the rep and comment
    I want to learn what you just did.. Can you guide me to a Youtube link/Blog or something where I can find more info about it?

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,685

    Re: Removing varied number of letters from end of number-letter string

    Quote Originally Posted by NeedForExcel View Post
    I want to learn what you just did.. Can you guide me to a Youtube link/Blog or something where I can find more info about it?
    Surely...

    Everything is standard in Regular Expression except the Pattern. Pattern alone we need to learn rest remains the same.

    First I will explain how the used Expression in the pattern works for this scenario.

    "[A-Z]*$"

    [A-Z] Any SINGLE Uppercase character From A To Z.
    * Matches the character zero or more times (Alpha above A-Z)
    $ Matches the end of the input

    Used the Replace property of Regexp to replace the matched data with nothing.

    Here is my preserved materials about Regular Expression

    https://blog.udemy.com/vba-regex/
    http://www.aivosto.com/vbtips/regex.html
    http://www.aivosto.com/regexpr/help/syntax.html
    http://www.aivosto.com/regexpr/help/tutorial.html
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-15-2015
    Location
    Bristol, England
    MS-Off Ver
    2013
    Posts
    4

    Re: Removing varied number of letters from end of number-letter string

    Sixthsense,

    That's awesome, thanks so much! I'm new to using macros so it is good to see how they can solve my problems, and in a much more effective way than a formula can.


    NeedForExcel, thanks for your response also; I think we are both in awe at Sixthsense's work

  13. #13
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Removing varied number of letters from end of number-letter string

    @ Sixthsense
    Thank you very much.. I'll go through it..

  14. #14
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Turkey
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: Removing varied number of letters from end of number-letter string

    Alternative

    Please Login or Register  to view this content.
    Last edited by Ali Kırksekiz; 04-15-2015 at 07:50 AM.

  15. #15
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Removing varied number of letters from end of number-letter string

    Quote Originally Posted by Ali Kırksekiz View Post
    Alternative

    Please Login or Register  to view this content.
    That is a great formulas aswell!

  16. #16
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    107

    Re: Removing varied number of letters from end of number-letter string

    look the solution by formula:

  17. #17
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    107

    Re: Removing varied number of letters from end of number-letter string

    see this files:
    keep last lettets.xlsx

+ 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: 12
    Last Post: 03-20-2013, 05:46 PM
  2. count number of letters in a string
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-11-2006, 01:55 PM
  3. [SOLVED] count number of letters in a string
    By Wiley in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-11-2006, 01:50 PM
  4. Calculating number of letters in a string ...
    By Larry Wallis in forum Excel General
    Replies: 4
    Last Post: 02-18-2005, 10:06 AM
  5. Calculating number of letters in a string ...
    By Larry Wallis in forum Excel General
    Replies: 4
    Last Post: 02-18-2005, 10:06 AM

Tags for this Thread

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