TRIM formula over multiple cells

  • Thread starter Thread starter David
  • Start date Start date
D

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
 
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
 
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
Click to expand...

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
 
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
 
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.
 
Back
Top