Chomp / Remove Characters after certain # of characters reached? - Excel Help & Excel Macro Help
SEARCH ENTIRE SITE LOADING..

OZGRID Excel Help & Excel Best Practices Forums

Information Helpful? Why Not Donate.

SPECIALS PAGE FOR BARGAINS | BUILD YOUR GOLF SWING | FREE CUSTOM FUNCTIONS ADD-IN


Download Active Data For Excel Demo


Go Back Excel Help & Excel Macro Help > HELP FORUMS > EXCEL HELP
HOME Register Forum Help Calendar Search For Today's Posts Mark Forums Read

Reply

Chomp / Remove Characters after certain # of characters reached?

ANSWERS TO SIMILAR QUESTIONS
Remove Last x Characters From StringForce New Active Cell When Maximum Number Of Characters Is ReachedRemove Characters From End Of String OnlyRemove characters



Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Create Excel dashboards quickly with Plug-N-Play reports.


 
Thread Tools Search this Thread
Old March 13th, 2006
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
essential_clix essential_clix is offline
Member
 
I'm a Spammer:
MS Office Version: 2003
Op System: Windows XP
Assumed Experience: Poor (Know The Very Basics)
Join Date: 18th April 2005
English is 1st Language:
Posts: 40 -- Threads: 12
Chomp / Remove Characters after certain # of characters reached?

Hi,

Is there a way to "chomp" or remove excess characters from a cell after a certain number of characters have been reached?

For instance, say Column D has 500+ cells, all of which contain alphanumeric data (about 70 characters including spaces). I need to mass-remove (VBA?) the characters that come after the first 55 characters. The first 55 characters (including spaces) would stay in place.

Make sense? If not, please let me know and I'll try my best to reword the question.

Thanks a bunch guys n gals,
EC

Last edited by essential_clix : March 13th, 2006 at 09:09.
Print [Post / Thread] Reply With Quote
Old March 13th, 2006
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
rbrhodes rbrhodes is offline
Have VBA, will travel
 
I'm a Spammer:
MS Office Version: 97 forwards
Op System: Win ME to Win XP
Assumed Experience: ...still learning...
Join Date: 24th December 2004
English is 1st Language: Yes
Location: Nanaimo, Vancouver Island, British Columbia, Canada
Posts: 1,745 -- Threads: 5
Re: Chomp / Remove Characters after certain # of characters reached?

Hi EC,

Supposing your data is in Col A then a formula version could be placed in Col ?

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
=If(LEN(A1)>55, LEFT(A1,55), A1)

This will echo a 55 char string from Col A. To trim it to values you would use Copy/Patespecial/Values/

A VBA version would be:

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
Sub fiftyfive() ' Get rownumber of lastrow of data Col A lastrow = Range("a65536").End(xlUp).Row ' Check row 1 to last row #, Col A For i = 1 To lastrow ' If longer than 55 chars If Len(Cells(i, 1)) > 55 Then ' Cut to 55 chars Cells(i, 1) = Left(Cells(i, 1), 55) End If ' Not longer than 55 - get next row Next i End Sub

This version would replace the original values. Change the line:

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
' Cut to 55 chars Cells(i, 1) = Left(Cells(i, 1), 55) To Cells(i, 2) = Left(Cells(i, 1), 55)

and it adds the new values to Col B...


Cheers,

dr
Print [Post / Thread] Reply With Quote
Old March 13th, 2006
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
essential_clix essential_clix is offline
Member
 
I'm a Spammer:
MS Office Version: 2003
Op System: Windows XP
Assumed Experience: Poor (Know The Very Basics)
Join Date: 18th April 2005
English is 1st Language:
Posts: 40 -- Threads: 12
Re: Chomp / Remove Characters after certain # of characters reached?

Hey thanks a million, man! The first code worked like a charm!

Thanks again and have a good one!
Print [Post / Thread] Reply With Quote
Old March 13th, 2006
pangolin's Avatar
pangolin pangolin is offline
Long Term Member
 
I'm a Spammer:
MS Office Version: 2003
Op System: Windows 95
Assumed Experience: Never assume anything
Join Date: 26th July 2004
English is 1st Language:
Posts: 1,035 -- Threads: 3
Re: Chomp / Remove Characters after certain # of characters reached?

Incidentally one easy way to "dynamically" control the character length of a cell is to use DATA>> VALIDATION>>SETTINGS and there select TEXT LENGTH (which works also for numerics)
Print [Post / Thread] Reply With Quote
Old March 13th, 2006
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
Krishnakumar Krishnakumar is offline
OzMVP
 
I'm a Spammer:
MS Office Version: 2000
Op System: Windows XP
Assumed Experience:
Join Date: 18th November 2004
English is 1st Language: No
Location: God's Own Country
Posts: 2,881 -- Threads: 28
Re: Chomp / Remove Characters after certain # of characters reached?

Hi

=LEFT(A1, LOOKUP(9.999E+307, FIND(" ",A1&" ",ROW($A$1:$A$55))))
__________________
Kris

My Excel Replies
Print [Post / Thread] Reply With Quote
Old March 31st, 2006
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
essential_clix essential_clix is offline
Member
 
I'm a Spammer:
MS Office Version: 2003
Op System: Windows XP
Assumed Experience: Poor (Know The Very Basics)
Join Date: 18th April 2005
English is 1st Language:
Posts: 40 -- Threads: 12
Re: Chomp / Remove Characters after certain # of characters reached?

Quote:
Originally Posted by pangolin
Incidentally one easy way to "dynamically" control the character length of a cell is to use DATA>> VALIDATION>>SETTINGS and there select TEXT LENGTH (which works also for numerics)
Man, I love learning new tricks like that! Thanks!
Print [Post / Thread] Reply With Quote
Reply Lifetime Upgrade To Ad Free Styles

   « PREVIOUS lookup error for calculated array results || Cursor Movement NEXT »
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +9. The time now is 01:39.


Powered by vBulletin Version 3.5.4
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.
Ozgrid is Not Associated With Microsoft. Ozgrid Retains the Rights to ALL Posts and Threads