TRIM formula over multiple cells

[David]

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

 

[Peo Sjoblom]

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

 

[T. Valko]

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.

 

[Gord Dibben]

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

 

[David]

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

 

[Gord Dibben]

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

 

[Bob Ryan]

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.

 

[ebloch]

Free ASAP Utilities has this function which works on the selected cell(s)

Eric