An Excel Question Please

[SpeckledJim]

I've a spreadsheet of 35,000 lines - they are automotive part numbers.

They are a jumble of different formats and alphanumerics and lengths. Ranging from 4 digits to 12 digits long.

Some have a suffix of 'G', and some have a suffix of 'LR', and some have no suffix at all.

I need to separate those three suffix groups into different spreadsheets.

Can you help me please!?

[Doofus]

Use =Right(A1,2) to put the suffixes into a new cell on the same row. A1 assumes that's where one of your part numbers is.

Copy that formula onto every row, then sort on that columm. Then you can cut and paste the list as needed.

[Mr Pointy]

Doofus said:

Use =Right(A1,2) to put the suffixes into a new cell on the same row. A1 assumes that's where one of your part numbers is.

Copy that formula onto every row, then sort on that columm. Then you can cut and paste the list as needed.

I'm a bit puzzled how that's going to work. Your formula will extract/copy the last two characters won't it, which might not give the desired result:

123456LR - This would extract LR
123456G - This would extract 6G
123456 - This would extract 56

Or have I got it wrong?

[lampchair]

Could you not just go basic, put a filter on, filter search to ‘LR’ cut those, same for ‘g’ cut those etc.

[bigpriest]

lampchair said:

Could you not just go basic, put a filter on, filter search to ‘LR’ cut those, same for ‘g’ cut those etc.

This - or just sort the whole lot, search for "LR" and "G" and insert a new row at that point. Then copy & paste into different sheets

[Welshbeef]

bigpriest said:

This - or just sort the whole lot, search for "LR" and "G" and insert a new row at that point. Then copy & paste into different sheets

This it will take you under 20 seconds

[Mr Pointy]

Assuming your first part number is in cell A1 then in A2 type the following formula into B1 & copy down:
=IF(RIGHT(A1,1)="R","LR", IF(RIGHT(B2,1)="G","G","None"))

This will return either LR, G or None in B1 & you can then sort the list. Note this will fail if any of the part numbers with no suffix have R or G as the last character.

[Mr Pointy]

Welshbeef said:

bigpriest said:

This - or just sort the whole lot, search for "LR" and "G" and insert a new row at that point. Then copy & paste into different sheets

This it will take you under 20 seconds

What happens if the alphanumeric part number contains "LR" or "G"? eg:
12345LR34G78

[Doofus]

Mr Pointy said:

Doofus said:

Use =Right(A1,2) to put the suffixes into a new cell on the same row. A1 assumes that's where one of your part numbers is.

Copy that formula onto every row, then sort on that columm. Then you can cut and paste the list as needed.

I'm a bit puzzled how that's going to work. Your formula will extract/copy the last two characters won't it, which might not give the desired result:

123456LR - This would extract LR
123456G - This would extract 6G
123456 - This would extract 56

Or have I got it wrong?

Yes. And when you sort it, all the LGs will be together, all the xGs will be together and all the numbers will be at the end.

[anxious_ant]

Mr Pointy said:

I'm a bit puzzled how that's going to work. Your formula will extract/copy the last two characters won't it, which might not give the desired result:

123456LR - This would extract LR
123456G - This would extract 6G
123456 - This would extract 56

Or have I got it wrong?

Based on your example what you probably need is a function which strips the numeric character.
Have a look at : https://exceljet.net/formula/strip-numeric-charact...

However if you have characters in between the numbers as per posted above you may run in to some issues.

[bigpriest]

Mr Pointy said:

What happens if the alphanumeric part number contains "LR" or "G"? eg:
12345LR34G78

Then the quick method won't work very well

Need to add a new column and work out what the suffix is.
=IF(LEFT(A1,1)="G","G",IF(LEFT(A1,2)="LR","LR",""))

[paulrockliffe]

SpeckledJim said:

I've a spreadsheet of 35,000 lines - they are automotive part numbers.

They are a jumble of different formats and alphanumerics and lengths. Ranging from 4 digits to 12 digits long.

Some have a suffix of 'G', and some have a suffix of 'LR', and some have no suffix at all.

I need to separate those three suffix groups into different spreadsheets.

Can you help me please!?

First up, format the 35,000 lines as a table, so Excel will automatically apply your formula(s) to the lot without messing around with copy-paste across a large area.

Secondly, is this a one-off fix of something that was setup by an idiot or an ongoing-thing? If you might need to do it again and you have Excel 2016 onwards, point Power Query at the table and you'll create a series of steps that can be used on the same data repeatedly.

Actually, I'd be tempted to use Power Query anyway as once you've written a suitable formula to extract the right bit from each string you can finish with a filter to create your three separate tables too.

If you do it that way you might realise you don't need three different tables anyway - If you drop the whole query output into the Data Model you can then create the nice output you want as a Pivot Table and drop the G, LR stuff out as a Slicer to let you filter the single table.

[CoolHands]

I presume that ^ was written to bamboozle the op

[98elise]

In a second column use the concatenate function to stick a non-alphanumeric character on the end of the numbers, say "£", but anything will do.

In that new column use the search filter to find "LR£", that's your first set of data.

Do the same for "G£", that's your second data set.

The rest is your non suffix data.

Edited by 98elise on Sunday 16th February 14:46

[Venier]

Just use the autofilter function on the column and then select cells with a G, LR and whatever else you want to select and copy and paste the results wherever

[SpeckledJim]

Mr Pointy said:

Assuming your first part number is in cell A1 then in A2 type the following formula into B1 & copy down:
=IF(RIGHT(A1,1)="R","LR", IF(RIGHT(B2,1)="G","G","None"))

This will return either LR, G or None in B1 & you can then sort the list. Note this will fail if any of the part numbers with no suffix have R or G as the last character.

Thanks all for all your input.

It's only bloody worked!

PH brains trust delivers once again. Thanks indeed.