TRIM formula over multiple cells

Hi all,

Does anyone know how to apply a text-specific TRIM formula in Excel
over multiple cells?

Specifically, I'm trying to apply =TRIM("text") to each cell in a
column without typing it into each cell.

If I use =TRIM("A2") version of the formula, I have to create another
column to place the values in, and I can't create another column on
this spreadsheet, even if I hide the original column.

Any ideas?

Thanks,
David
0
dhd2005 (2)
11/11/2009 6:16:30 PM
excel 39879 articles. 2 followers. Follow

7 Replies
668 Views

Similar Articles

[PageSpeed] 7

Use a macro if you do it on a regular basis


http://www.mvps.org/dmcritchie/excel/join.htm#trimall



that way you just select the range you want to change and run the macro, 
save it in your personal.xls and it will be available for all workbooks


here's how to install macros


http://www.mvps.org/dmcritchie/excel/install.htm




-- 


Regards,


Peo Sjoblom


"David" <dhd2005@gmail.com> wrote in message 
news:816bd599-21c7-4dcf-9793-adb5ca3ffe24@h10g2000vbm.googlegroups.com...
> Hi all,
>
> Does anyone know how to apply a text-specific TRIM formula in Excel
> over multiple cells?
>
> Specifically, I'm trying to apply =TRIM("text") to each cell in a
> column without typing it into each cell.
>
> If I use =TRIM("A2") version of the formula, I have to create another
> column to place the values in, and I can't create another column on
> this spreadsheet, even if I hide the original column.
>
> Any ideas?
>
> Thanks,
> David 


0
11/11/2009 6:26:23 PM
How about a macro?

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

It's very simple to use. Install the macro, select the range of cells you 
want to trim, run the macro.

-- 
Biff
Microsoft Excel MVP


"David" <dhd2005@gmail.com> wrote in message 
news:816bd599-21c7-4dcf-9793-adb5ca3ffe24@h10g2000vbm.googlegroups.com...
> Hi all,
>
> Does anyone know how to apply a text-specific TRIM formula in Excel
> over multiple cells?
>
> Specifically, I'm trying to apply =TRIM("text") to each cell in a
> column without typing it into each cell.
>
> If I use =TRIM("A2") version of the formula, I have to create another
> column to place the values in, and I can't create another column on
> this spreadsheet, even if I hide the original column.
>
> Any ideas?
>
> Thanks,
> David 


0
biffinpitt (3172)
11/11/2009 6:30:09 PM
Another macro.

Sub TRIM_EXTRA_SPACES()
Dim Cell As Range
    For Each Cell In Selection
        If (Not IsEmpty(Cell)) And _
                Not Application.IsNumber(Cell.Value) And _
                InStr(Cell.Formula, "=") = 0 _
                Then Cell.Value = Application.Trim(Cell.Value)
    Next
End Sub

On Wed, 11 Nov 2009 10:16:30 -0800 (PST), David <dhd2005@gmail.com> wrote:

>Hi all,
>
>Does anyone know how to apply a text-specific TRIM formula in Excel
>over multiple cells?
>
>Specifically, I'm trying to apply =TRIM("text") to each cell in a
>column without typing it into each cell.
>
>If I use =TRIM("A2") version of the formula, I have to create another
>column to place the values in, and I can't create another column on
>this spreadsheet, even if I hide the original column.
>
>Any ideas?
>
>Thanks,
>David

0
Gord
11/11/2009 8:44:01 PM
On Nov 11, 1:16=A0pm, David <dhd2...@gmail.com> wrote:
> Hi all,
>
> Does anyone know how to apply a text-specific TRIM formula in Excel
> over multiple cells?
>
> Specifically, I'm trying to apply =3DTRIM("text") to each cell in a
> column without typing it into each cell.
>
> If I use =3DTRIM("A2") version of the formula, I have to create another
> column to place the values in, and I can't create another column on
> this spreadsheet, even if I hide the original column.
>
> Any ideas?
>
> Thanks,
> David

Hey all,

Thank you for the quick responses, that was a big help! However, I'm
running into another problem. Unfortunately, I'm forced to run a Mac
at work all day. I know, I know, bring on the jokes. It's not my
choice. Anyway, Macs somehow don't support macros, since they're just
fancy, expensive etch-a-sketches. Is anyone aware of a way to work
around this, or is there another solution to applying the TRIM
function to multiple cells without using a macro?

Thanks again,
David
0
dhd2005 (2)
11/11/2009 9:38:24 PM
Without code, you need a helper column.

"Can't create another column"

Why not?

Workaround...............

Insert a new worksheet.

In A1 enter  =TRIM(Sheet1!A2)

Copy down as far down as you wish using fill handle.

Copy that range then Paste Special>Values onto Sheet1 A2

Delete new sheet.


Gord


On Wed, 11 Nov 2009 13:38:24 -0800 (PST), David <dhd2005@gmail.com> wrote:

>On Nov 11, 1:16�pm, David <dhd2...@gmail.com> wrote:
>> Hi all,
>>
>> Does anyone know how to apply a text-specific TRIM formula in Excel
>> over multiple cells?
>>
>> Specifically, I'm trying to apply =TRIM("text") to each cell in a
>> column without typing it into each cell.
>>
>> If I use =TRIM("A2") version of the formula, I have to create another
>> column to place the values in, and I can't create another column on
>> this spreadsheet, even if I hide the original column.
>>
>> Any ideas?
>>
>> Thanks,
>> David
>
>Hey all,
>
>Thank you for the quick responses, that was a big help! However, I'm
>running into another problem. Unfortunately, I'm forced to run a Mac
>at work all day. I know, I know, bring on the jokes. It's not my
>choice. Anyway, Macs somehow don't support macros, since they're just
>fancy, expensive etch-a-sketches. Is anyone aware of a way to work
>around this, or is there another solution to applying the TRIM
>function to multiple cells without using a macro?
>
>Thanks again,
>David

0
Gord
11/12/2009 12:37:00 AM
David - To specifically answer the part of your question "I'm trying to apply 
=TRIM("text") to each cell in a column without typing it into each cell", 
have you considered an array formula?

Assume untrimmed text is in cells A1:A7, and I want trimmed text in cells 
B1:B7, but only want to type the formula once. i would highlight cells B1:B7, 
type the formula -TRIM(A1:A7) and press SHIFT + CTRL + ENTER keys 
simultaneously.


"David" wrote:

> On Nov 11, 1:16 pm, David <dhd2...@gmail.com> wrote:
> > Hi all,
> >
> > Does anyone know how to apply a text-specific TRIM formula in Excel
> > over multiple cells?
> >
> > Specifically, I'm trying to apply =TRIM("text") to each cell in a
> > column without typing it into each cell.
> >
> > If I use =TRIM("A2") version of the formula, I have to create another
> > column to place the values in, and I can't create another column on
> > this spreadsheet, even if I hide the original column.
> >
> > Any ideas?
> >
> > Thanks,
> > David
> 
> Hey all,
> 
> Thank you for the quick responses, that was a big help! However, I'm
> running into another problem. Unfortunately, I'm forced to run a Mac
> at work all day. I know, I know, bring on the jokes. It's not my
> choice. Anyway, Macs somehow don't support macros, since they're just
> fancy, expensive etch-a-sketches. Is anyone aware of a way to work
> around this, or is there another solution to applying the TRIM
> function to multiple cells without using a macro?
> 
> Thanks again,
> David
> .
> 
0
BobRyan (7)
11/12/2009 1:29:01 AM
Free ASAP Utilities has this function which works on the selected cell(s)

Eric


"David" <dhd2005@gmail.com> wrote in message 
news:816bd599-21c7-4dcf-9793-adb5ca3ffe24@h10g2000vbm.googlegroups.com...
> Hi all,
>
> Does anyone know how to apply a text-specific TRIM formula in Excel
> over multiple cells?
>
> Specifically, I'm trying to apply =TRIM("text") to each cell in a
> column without typing it into each cell.
>
> If I use =TRIM("A2") version of the formula, I have to create another
> column to place the values in, and I can't create another column on
> this spreadsheet, even if I hide the original column.
>
> Any ideas?
>
> Thanks,
> David 

0
ericNOSPAM (46)
11/12/2009 6:31:35 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

multiple email identities
HELP! My husband and I share the same computer and we have our email come to our inbox through Microsoft Outlook. However it is merging our accounts- how do we seperate into 2 identities so we each get our own mail! It is so frustrating! Please help! Thanks Kat Outlook does not use Identities - it uses profiles. See this page for more information: http://www.slipstick.com/outlook/share.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After s...

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: http://www.cpearson.com/excel/SortByColor.htm --- Regards, Norman "swiftcode" <swiftcode@discussions.microsoft.com> wrote in message news:FC1550A7-A8DD-4EC0-B171-F1DB4373C35C@microsoft.com... > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...