Filter column for content longer than 12 characters?

%3CLINGO-SUB%20id%3D%22lingo-sub-1310559%22%20slang%3D%22en-US%22%3EFilter%20column%20for%20content%20longer%20than%2012%20characters%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1310559%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20are%20working%20a%20spreadsheet%20with%26nbsp%3B%4030K%20rows%20of%20data.%26nbsp%3B%20We%20have%20an%20issue%20that%20we%20cannot%20process%20a%20certain%20column%20if%20more%20than%2012%20Characters.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1st%20task%20is%20to%20filter%20this%20column%20for%20any%20cells%20longer%20than%2012%20characters.%3C%2FP%3E%3CP%3E2nd%20task%20if%20even%20possible%2C%20is%20to%20remove%20characters%20from%20the%20front%20end%20of%20content%20down%20to%2012%20characters.%3C%2FP%3E%3CP%3E*These%20are%20Serial%20numbers%2C%20so%20the%20more%20important%20data%20are%20the%20last%204-6%20characters%20and%20cannot%20be%20lost.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%2Fhelp%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1310559%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1310705%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20column%20for%20content%20longer%20than%2012%20characters%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1310705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F623217%22%20target%3D%22_blank%22%3E%40RobertHuskey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20should%20be%20able%20to%20use%20the%20%22%3DRIGHT()%22%20Function%20to%20accomplish%20the%20task.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20syntax%20for%20it%20is%20%3DRIGHT(A1%2C12)%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EA1%20is%20just%20the%20cell%20that%20you%20want%20to%20find%20contents%20the%20%22right%22%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3ESo%20if%20your%20serial%20number%20is%20for%20example%20%2200123456789XC6%22%2C%20and%20you%20want%20the%20last%2012%20characters.%20Then%20using%20the%20above%20formula%20will%20return%20%22123456789XC6%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20would%20have%20to%20put%20the%20formula%20in%20a%20separate%20column%2C%20but%20then%20after%20it%20calculates%20you%20can%20copy%20over%20the%20values%20and%20paste%20as%20text%20in%20the%20correct%20column%20if%20desired.%20Additionally%2C%20this%20formula%20would%20not%20mess%20up%20any%20of%20the%20other%20cells%20were%20the%20numbers%20are%20less%20than%2012%20as%20it%20would%20include%20everything%20at%20that%20point.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20let%20me%20know%20if%20this%20doesn't%20help%20or%20if%20you%20have%20additional%20questions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegards%2C%3C%2FP%3E%0A%3CP%3E-Jack%20B.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1310917%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20column%20for%20content%20longer%20than%2012%20characters%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1310917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F623242%22%20target%3D%22_blank%22%3E%40Jack_Bumgarner%3C%2FA%3E%26nbsp%3BThank%26nbsp%3B%20you.%26nbsp%3B%20Adding%20the%20column%2C%20using%20the%20formula%2C%20etc%20worked%20and%20task%20is%20completed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

We are working a spreadsheet with @30K rows of data.  We have an issue that we cannot process a certain column if more than 12 Characters.

 

1st task is to filter this column for any cells longer than 12 characters.

2nd task if even possible, is to remove characters from the front end of content down to 12 characters.

*These are Serial numbers, so the more important data are the last 4-6 characters and cannot be lost.

 

Thoughts/help?

 

Thank you.

2 Replies
Highlighted

@RobertHuskey 

 

Hello,

 

You should be able to use the "=RIGHT()" Function to accomplish the task.

 

The syntax for it is =RIGHT(A1,12)

  • A1 is just the cell that you want to find contents the "right"

So if your serial number is for example "00123456789XC6", and you want the last 12 characters. Then using the above formula will return "123456789XC6"

 

You would have to put the formula in a separate column, but then after it calculates you can copy over the values and paste as text in the correct column if desired. Additionally, this formula would not mess up any of the other cells were the numbers are less than 12 as it would include everything at that point.

 

Please let me know if this doesn't help or if you have additional questions.

 

Regards,

-Jack B.

Highlighted

@Jack_Bumgarner Thank  you.  Adding the column, using the formula, etc worked and task is completed.