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

**WhiskeyGolf** · 04-15-2015, 04:57 AM

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

**NeedForExcel** · 04-15-2015, 05:01 AM

Hi,

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

**WhiskeyGolf** · 04-15-2015, 05:18 AM

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?

**NeedForExcel** · 04-15-2015, 05:21 AM

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?

**WhiskeyGolf** · 04-15-2015, 05:23 AM

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

**NeedForExcel** · 04-15-2015, 05:24 AM

Can you guide me how?

**:) Sixthsense :)** · 04-15-2015, 05:41 AM

How to install your new code

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
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)

**NeedForExcel** · 04-15-2015, 05:50 AM

@

Sixthsense

That is insane..

**:) Sixthsense :)** · 04-15-2015, 05:54 AM

Originally Posted by NeedForExcel

That is insane..

Thanks for the rep and comment

**NeedForExcel** · 04-15-2015, 06:07 AM

Originally Posted by :) Sixthsense :)

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?

**:) Sixthsense :)** · 04-15-2015, 06:24 AM

Originally Posted by NeedForExcel

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

**WhiskeyGolf** · 04-15-2015, 06:51 AM

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

**NeedForExcel** · 04-15-2015, 07:11 AM

@

Sixthsense

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

**Ali Kırksekiz** · 04-15-2015, 07:44 AM

Alternative

Please Login or Register  to view this content.

**NeedForExcel** · 04-15-2015, 07:51 AM

Originally Posted by Ali Kırksekiz

Alternative

Please Login or Register  to view this content.

That is a great formulas aswell!

**salim ali** · 04-15-2015, 08:22 AM

look the solution by formula:

**salim ali** · 04-16-2015, 04:43 AM

see this files:
keep last lettets.xlsx

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Extracting Letter/Number from Alphanumeric string (4 different times from same string)

[SOLVED] count number of letters in a string

count number of letters in a string

[SOLVED] Calculating number of letters in a string ...

Calculating number of letters in a string ...

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions