Need help with excel (removing grammar and max cell length)?

BWA@CSFB · 05-18-2006, 09:12 AM

Hi,

I really need some help with something that I am doing in Excel.

I suspect that Excel does not have the functionailty to do this, but if it does - it would be so cool.

I have a column in which I need to ensure that any populated cells are free from any punctuation marks e.g. full-stops, commas brackets etc.

Can this be removed with a function or process or do I have to remove them manually?

Also, I need to ensure that no cell contains a string that is more than 50 characters in length. Can I do a formula to remove any characters after the 50 way point?

Any help would be VERY much appriciated.

Many thanks,

Brendon.

**ReeKorl** · 05-18-2006, 09:49 AM

For the first item, you can use the "Replace..." functionality of Excel. Select the column you want to remove these from, and click Edit -> Replace...

In here, put a comma in the first box, leave the second box blank, and hit Replace All. Repeat for the other punctuation you want removed. I know there's a formula you could put together to strip out the unwanted characters, but it's a little convoluted and I can't remember it off the top of my head. If I can find how I put it together, I'll post it for you.

As for the second, to stop people entering data over 50 characters, you can set up the cells to do a bit of data validation. Select the cells you want this on, and click Data -> Validation...

Select Allow: Text Length, Data: Less than or equal to, Maximum: 50. On the Error Alert tab, put in whatever message you want to be displayed if the input is too long.

If you already have the data input and want it stripped down to 50 characters, create a new formula to the right of the existing data, and enter the following formula:

Code:

=LEFT(A1,50)        'assuming the start of the data in in cell A1

and autofill this down to the bottom of the data. Copy and "Paste Special..." the data into the existing cells and select Values. Make sure you do this before turning on the validation, or you may wipe that out with the paste.

Of course, you don't need to copy and paste the data, you could just reference the formula column instead of the raw data column.

BWA@CSFB · 05-19-2006, 04:04 AM

Hi Reekorl,

The advice that you gave me is great - thank you very much.

The punctuation thing is good - nice and simple, but I have encountered a problem with the 50 charater thing.

What I need to do is in one column ensure that no cells contain a string length of more than 50 characters.

While I can do what you have suggested regarding adding a column to the left and then using the fomula, I then need to copy the data back into that column and delete the added column. In short, I don't want to add columns to my spreadsheet.

Can I do the fomula thing and then paste the actual data back into the column to the left, rather than paste the foumula to the left.

Or can you tell Excel to highlight which columns are over 50 characters and then you can edit the data down to 50 characters?

Many thanks,

Bren.
What I need to do is

Anne Troy · 05-19-2006, 07:30 AM

Bren: Can you follow this?

http://www.ozgrid.com/forum/showthread.php?t=47902

05-18-2006, 09:12 AM	#1 (permalink)
BWA@CSFB Registered User Join Date: Sep 2005 Posts: 19 OS: XP	Need help with excel (removing grammar and max cell length)? Hi, I really need some help with something that I am doing in Excel. I suspect that Excel does not have the functionailty to do this, but if it does - it would be so cool. I have a column in which I need to ensure that any populated cells are free from any punctuation marks e.g. full-stops, commas brackets etc. Can this be removed with a function or process or do I have to remove them manually? Also, I need to ensure that no cell contains a string that is more than 50 characters in length. Can I do a formula to remove any characters after the 50 way point? Any help would be VERY much appriciated. Many thanks, Brendon.

05-18-2006, 09:49 AM	#2 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	For the first item, you can use the "Replace..." functionality of Excel. Select the column you want to remove these from, and click Edit -> Replace... In here, put a comma in the first box, leave the second box blank, and hit Replace All. Repeat for the other punctuation you want removed. I know there's a formula you could put together to strip out the unwanted characters, but it's a little convoluted and I can't remember it off the top of my head. If I can find how I put it together, I'll post it for you. As for the second, to stop people entering data over 50 characters, you can set up the cells to do a bit of data validation. Select the cells you want this on, and click Data -> Validation... Select Allow: Text Length, Data: Less than or equal to, Maximum: 50. On the Error Alert tab, put in whatever message you want to be displayed if the input is too long. If you already have the data input and want it stripped down to 50 characters, create a new formula to the right of the existing data, and enter the following formula: Code: =LEFT(A1,50) 'assuming the start of the data in in cell A1 and autofill this down to the bottom of the data. Copy and "Paste Special..." the data into the existing cells and select Values. Make sure you do this before turning on the validation, or you may wipe that out with the paste. Of course, you don't need to copy and paste the data, you could just reference the formula column instead of the raw data column. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

05-19-2006, 04:04 AM	#3 (permalink)
BWA@CSFB Registered User Join Date: Sep 2005 Posts: 19 OS: XP	Hi Reekorl, The advice that you gave me is great - thank you very much. The punctuation thing is good - nice and simple, but I have encountered a problem with the 50 charater thing. What I need to do is in one column ensure that no cells contain a string length of more than 50 characters. While I can do what you have suggested regarding adding a column to the left and then using the fomula, I then need to copy the data back into that column and delete the added column. In short, I don't want to add columns to my spreadsheet. Can I do the fomula thing and then paste the actual data back into the column to the left, rather than paste the foumula to the left. Or can you tell Excel to highlight which columns are over 50 characters and then you can edit the data down to 50 characters? Many thanks, Bren. What I need to do is

05-19-2006, 07:30 AM	#4 (permalink)
Anne Troy Registered User Join Date: Apr 2006 Location: Columbia, MD Posts: 81 OS: XP	Bren: Can you follow this? http://www.ozgrid.com/forum/showthread.php?t=47902