Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Trim Function Not Working

This Trim funcion is not working for me for some reason. I have attached a sample file. Can someone help me with this? You can test the macro to see. I just want to delete the trailing blank characters on Column A. Here is the code.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Trim Not Working
[data] ...

I am having problems removing "2009-642" from the above text string(s) (doesn't matter which)

This is just a sample line and it is not always in the same place so I am afraid a simple mid function from a set starting point will not suffice, the number is also dynamic in size, location and leading digits.

As a result I have created a formula to get rid of the "1 XS0444499197" part of the formula leaving me with " 2009-642 "

Now unfortunately I need to get rid of the spaces (or what look like spaces) just leaving me with the number but infuriatingly I can not get the trim function to work, so I can only guess that it is not in fact reading these spaces as spaces, and I'm at a bit of a loss how to proceed.

View Replies!   View Related
TRIM Not Working (data With Spaces)
My V-lookups are not working. I have data with extra spaces before or after the word. The table I am looking up does not have extra spaces. I'm trying to to a TRIM(data with spaces) and then copy and paste the values so that the spaces go away and the TRIM is not taking my spaces out.

View Replies!   View Related
Using TRIM Function
I'm trying to clean up a very large spreadsheet that contains a tremendous amount of empty space.

I tried using the TRIM function to delete some of the additional space within each cell, but it only seems to allow you to update one cell/column at a time.

Is there a way to clean up the entire spreadsheet at once?

Find/Replace all spaces won't work because some cells have multiple words, and they need the space in between.

View Replies!   View Related
Trim Function Parameters
what is the function of the (1) within the below trim function.

The value of A2 is "ABC - 123 - Lovely"

Range("A1").Value = Trim( Split(Range("A2").Value, "-")(1))

View Replies!   View Related
Trim Function Is Slow
Due to the fact that some of the raw data I get dumped out of my database has leading and trailing spaces it is necessary to trim them. The one part of my code which seems to take forever to run is the section which performs this function on around 22,000 cells in one column. Here is the code I'm using. Is there a better way to do this?

'Set ServiceType = wsTmp.Range("C2:C" & tmpRows)
'For Each cell In ServiceType
'cell.Value = Trim(cell.Value)
'Next cell

The thing, with much code, seems to be that the slowness of my computer in executing code is directly proportional to the amount of uptime on the machine. Often a cold start will remedy the issue. Rather than having to do this daily to remedy the situation is there some other remedy to the problem?

View Replies!   View Related
TRIM Function And Leading Spaces
I have some cells containing imported text with a lot of leading spaces. I try to get rid of them by TRIM'ing those cells, but it doesn't work.
Is TRIM only for trailing spaces?

View Replies!   View Related
Clean & Trim Function In VBA
"clean" function is not recognized.

Sub ConvertToTrim()
For Each rng In Selection
rng.Value = Clean(Trim(rng.Value))
Next rng
End Sub

View Replies!   View Related
Trim Macro: Trim All Of The Data From Rows 2:30 Removing Any Trailing Spaces After The Last Word In Each Cell
I have come up with this to Trim all of the data from rows 2:30 removing any trailing spaces after the last word in each cell. The macro takes a couple of minutes to run have I got something wrong that is making it run slowly or does the Trim process just take longer?

Dim myRange As Range
Dim myRow As Range
Set myRange = Range("2:30")
If myRange Is Nothing Then Exit Sub
Application. ScreenUpdating = False
myRange.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
For Each myRow In myRange.Columns
If Application. CountA(myRow) > 0 Then
myRow.TextToColumns Destination:=myRow(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next myRow
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
VBA Function Not Working
I copied this function from a VBA book and I get a Error 91 "object variable or with Block variable not set"

I have made sure that Microsoft scripting is referenced.
any clues would be appreciated.
I might add that this is a follow up on a thread I discussed with "Datasmart", thanks to John for getting me started.

Function BrowseForfolderShell() As String
Dim objshell As Object, objfolder As Object
Set obshell = CreateObject("Shell.Application")
'Uncomment next line to start at desktop
'Set objfolder = objshell.BrowseForFolder(0, "Please Select a Folder", 0, 0)
Set objfolder = objshell.BrowseForFolder(0, "Please select a Folder", 0, "c:")
If (Not objfolder Is Nothing) Then

View Replies!   View Related
Date Function Is Not Working..
I have set of data in which i want to put filter Milestones Type <> Milestone
and a filter Actual date is greater than or equal to 30 days ago and is less than or equal to today.

View Replies!   View Related
Array Function Test Not Working
Ok, I'm sure that there is something really obvious here, but I'm just not seeing it....

Test Data starting at cell A1:

View Replies!   View Related
DMIN Function Not Working As Expected
I'm new to the DMIN function and I'm having a problem.

Cell A2: Date (rest of column is date format) (heading for the column)
Cell A2: Credit (rest of column is acctg. format) (heading for the column)
Cell A2: Debit (rest of column is acctg. format) (heading for the column)
Cell A2: Balance (rest of column is, equals balance field from previous row + credit - debit for current row, acctg. format) (heading for the column)
Column E: Notes (rest of column is generic text) (heading for the column)

Cell K1: Date (the text "Date")
Cell K2: > TODAY() (the text "> TODAY()") -- maybe this should be ="> TODAY()"?

Cell H2: =DMIN(A2:D1000,"Date",K1:K2)

I'm trying to get the minimum balance for the Balance column where the date is greater than today (lowest balance that I have in the future, which helps me see if I will be overdrafting).

I'm getting a value of 0 in H2. I have also tried =DMIN(A2:D1000,1,K1:K2) and get the same result.

View Replies!   View Related
Calculate Working Days Function
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.

View Replies!   View Related
>>> Sound User Function Not Working
I attempted to create a user function as per the link that VBA Noob posted in this post

I get #Name error. I placed the wav file Applause.wav in the same folder as the workbook. What did I do wrong?. Also, assuming I get this problem sorted out, how would I represent the condition "pass" or "fail" as in cell B3 in the Alarm formula? Would it be; Alarm(B3,"Pass") or Alarm(B3,"=Pass") or something else?

View Replies!   View Related
Aggregate Function On Vlookup Array Not Working
I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate.

Cells A1 to B5 contain a lookup table:

table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}

LetterNumber D4 C3 B2 A1

Cells A7 to C8 contain the input values:

table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}..................

View Replies!   View Related
Validation And Lookup Function Are Working Weird
There is a range of quarters and relevant dates like from and till - means if you have 1 Q 2009 => it means from 01.01.09 till 31.03.09. I would like to have following in my excel. There is a validation for selecting Q from list, once you select Q, there is LOOKUP function displaying from and till dates. But however it's not working, in same cases it's working, but in most cases the from/till period is not relevant to selected quarter.

View Replies!   View Related
Excel 2007 Net Working Days Function
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.

View Replies!   View Related
Working Day Function: (Day + Next 1st Working Day)
I was looking for a final result as follows

21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)

21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)

View Replies!   View Related
Reformatting Date Into Number. Reformat Cell Not Working. Function Maybe?
I am rewriting functions for my companies inventory spreadsheets. Excel for some reason took the item codes and formatted them into date format. Right now it is in 10/01/1902 format, and it needs to be in 10-1902 format. I've tried reformatting the cells but so far I haven't found one that works.

View Replies!   View Related
UDF Working When Called From Sub, Doesn't Work As Worksheet Function
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.

For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.

My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.

View Replies!   View Related
Macros That Were Working In Excel 2003 Are Not Working In 2007
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.

Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i

View Replies!   View Related
Trim Text
I would like to trim text based on the character .

For example i have a code FGH45679.A How do I keep only the characters before .A without the dot?

View Replies!   View Related
Trim In Column
I am trying to record a macro, using the macro recorder, that will trim the contents of the cells in one column. The starting cell will always be E12 and the ending cell will vary depending on the number of records that month.

After I select cell E12, I hold down the shift key and hit end and then the down arrow to get my range. After that, I'm trying to use the Insert - Function from the menu to setup the trim but can't get it to work.

View Replies!   View Related
Trim / Mid / Right Etc
Sample of Column A:

Sat 15th Sep 07 14:15 Bath Rugby 29 - 15 Worcester Warriors Recreation Ground 10,010
Sat 15th Sep 07 15:30 Harlequins 35 - 27 London Irish Twickenham 39,400 Report
Sat 15th Sep 07 18:00 London Wasps 19 - 29 Saracens Twickenham 39,400 Report
Sun 16th Sep 07 15:00 Bristol Rugby 13 - 26 Leicester Tigers Memorial Stadium 8,125
Sun 16th Sep 07 15:00 Leeds Carnegie 24 - 49 Gloucester Rugby Headingley Carnegie
Sun 16th Sep 07 15:00 Newcastle Falcons 33 - 12 Sale Sharks Kingston Park 5,859 Report

Trying to display in column B and C:

Bath Rugby Worcester Warriors
Harlequins London Irish
London Wasps Saracens

Because of the varying length of text each time,
i'm having problems doing this.

Tried =MID(A1,FIND(":",A1)+4,25)
but obviously get extra text other than team name.

View Replies!   View Related
VBA Trim
I would like to have a simple basic VBA trim function with no complications at all. Just a simple trim vba code like:

=trim( Selected/Active cells)

But I just couldn't think of how to do it, even with recording.

View Replies!   View Related
Using Trim On A Selection
I want to create a macro that will allow me to highlight a column and have the macro Trim every cell with text in the column, preferably putting the results over the original text. The column in question has text in every cell, until the column ends.

(That is, there are no numbers and no blanks until the data ends altogether.) I do not want to remove internal spaces in the text, just the leading and trailing ones.

I am sure this is fairly simple, but I'm not sure how to get it to look at every cell and then terminate properly.

View Replies!   View Related
TRIM + IF Formula
I am trying to make a simple quiz on a spreadsheet which will provide the score immediately the player has finished. Basically a row is as follows:

C5= Question; D5 = player's input answer, E5 = check answer and give score

For example:
in C5 "What colour is a banana?"
The player inputs the answer to D5: yellow
in E5, the answer is tested by a formula and score is given. For this I have a formula IF(D5="yellow",1,0) hence E5 returns either 1 or 0.

Now my problem: If the player inputs one or more spaces, I need to use the TRIM function as well otherwise the answer will be incorrect. How can I work the TRIM with the IF formula?

A thought has just occurred to me that I might have to firstly Copy and TRIM the answer in to E5 and then have the IF condition in F5, but that needs another column and seems a longer way around.

View Replies!   View Related
Trim In By The Players
Iam pulling hockey stats from yahoo sports into excel on one tab then i have other tabs as teams and iam pulling the stats from the yahoo sports tab to them

the yahoo tab is called Players but when yahoo bringis in the players names they come with a space in front of them. Now the formula iam using works if i go to the players tab and take the space out but as soon as I refresh the data it puts the space back.

here is what iam using
=IF(ISNUMBER(MATCH(TRIM($D5), Players!$A$1:$A$635,0)),INDEX(Players!NHL_2010_skaters, MATCH(TRIM($D5), Players!$A$1:$A$635,0), MATCH(E$4, Players!$A$1:$AT$1, 0)),0)

I think i have to put trim in by the players but when i try and add it I get formula errors.

View Replies!   View Related
Trim + Date
I would like to have in a title cell "The Week of 8/24/09 - 8/28/09". I want to use something like this

View Replies!   View Related
Trim All Cells In A Sheet
I'm trying to trim all the data in my worksheet in order to prepare it for several steps of further analysis. Running the below code will cause an error that I don't understand. It says:

Runtime error '1004': Application-defined or object-defined error

the code is (error causing line indicated in comments):

Sub trimAll()
    Application.ScreenUpdating = False
    Dim Rows As Long
    Dim Column As Long
    Dim i As Integer
        Rows = ActiveSheet.UsedRange.Rows.Count
        Column = ActiveSheet.UsedRange.Columns.Count
    For i = 1 To Column
            Selection.Insert Shift:=xlToRight
            ActiveSheet.Range(Cells(1, i), Cells(Number, i)) = "=TRIM(RC[1])"  ** ERROR **
            Columns(i + 1).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
            Selection.Delete Shift:=xlToLeft
    Next i
    Application.ScreenUpdating = True
End Sub 

View Replies!   View Related
Trim Text In VBA
I have a small Excel VBA program that pulls data from our company database. I use this to collect information about orders that have been placed. The decriptions of our inventory within our accounting software usually go something like this: [

2/24] Small Red Rose
The [2/24] stands for 24 pieces per case and 2 pieces per inner pack.

My question is... can I trim off the text '[2/24]' within VBA?

I need to create quotes, labels and other things that use these descriptions and don't need to include the information with the brackets.

Obviously within VBA everything is done with variables, such as strDESC for the description.

View Replies!   View Related
I'm trying to remove leading spaces in cells that have numbers. I'm familiar with the functions TRIM and CLEAN but they don't seem to work on numbers. Unfortunately, just reformatting the cell as text doesn't work.

View Replies!   View Related
Trim Red Text
Any idea how you would remove all text strings that are red, I want to leave any text within the cells that are not red.


A1 contains

15245, 1535554, 17548789, erg, rtree, bf5256s

Would become

15245, erg, bf5256s

View Replies!   View Related
Trim Left Of Cell Only
In cell B8 - I have a cell with the following info:
Mr A. Jones
4 spaces, ' Mr A.' , 4 spaces and 'Jones'

In order to Trim Only on the spaces to the left of the first letter in the cell I am using the following formula:

Can anyone tell me if there is an easier ay to do this ( I'm sure there must be )

View Replies!   View Related
Trim The Text In Vba
I have this code that trims cells and I would like to implement in it a way to remove line returns in cells (new lines created with alt+enter).
Below is the code I used so far:

View Replies!   View Related
Removing Character Using Trim..
I need your guys expertise in the following formula. I'm applying the following to a large range of data that varies in lenght,however all of the data has a 1Y or 2Y at the end of it. I need to remove it from the data into a new column. Currently I'm using =trim(mid(A2,1,30) how can I change my formula to obtain my results?

for example: Nationwide Select premium $74.99 1Y
Nationwide Select Premium W/e-mail $74.99 1Y

As you can see the lenght of the data is different.

View Replies!   View Related
Trim Text To X Characters
I have a cell which holds text with a maximum number of characters of 6. Letīs say I want this cell to hold a word which is bigger, and trim it till it has 6 characters.

Example: word "Hippopotamus" (longer than 6 characters) should be trimmed so its displayed as "Hippop"(6 characters)

is there a way to specify how i want to trim, either right or left?
Example "Hippop"(6 characters trim right) or "otamus" (6 characters trim left)

View Replies!   View Related
Trim Left Of Space
Sub GetThatstring()
Dim strMonth As String
Dim iYear As Integer
Dim StrMnthYear As String

strMonth = Trim(Left( Range("A1"), _
InStr(1, Range("A1"), " ", vbTextCompare)))
MsgBox strMonth

iYear = Mid(Range("A1"), _
InStr(1, Range("A1"), " ", vbTextCompare) + 1, 4)
MsgBox iYear

StrMnthYear = Trim(Left(Range("A1"), _
InStr(1, Range("A1"), " ", vbTextCompare) + 4))
MsgBox StrMnthYear
End Sub

I have a column of cells likes:
5/5058 Jack Daniels
5/29AA Crown Royal

I want to delete everything to the left of the first space.
So it will look like:
Jack Daniels
Crown Royal

It has to be vba though. I have done alot of trim in just formulas but this is first time in VBA. The above code is one I found by searching but not sure what to do to modify it.
This will be for range AM2:AM2000

View Replies!   View Related
Trim Numeric Characters After The Decimal
I have something simple (i think) but i cant think it through. I have a range of cells that are populated by a link and so even though the value shows #,### It really shows #,###.########

I would like to trim everything after the decimal point without having to adjust format of the range since some cells are general format and others are Percentage format but they all suffer from .####### HOWEVER, the last digits are variable and never the exact same amount of characters.

for each cell in range("C4:J9")
if cell.value "" then
end if
next cell

View Replies!   View Related
Data Cleansing TRIM Spaces
Suppose I have a list (mine is several thousand lines) with city names. In this list some has manually input some names with double spaces between multiple word names -- Example (using dashes to represent spaces in this case):


I know about the function TRIM, but that only works at the start or end, I need something simple to trim the extra space between two words.

View Replies!   View Related
Trying To Trim Anything Non-alphaneumeric From Target Cell
I'm trying to make up a sheet that will be able to cope with users pasting in data from other sources. I thought simply running a TRIM function on any data entered in the target cell would be enough, but I've realized that many of the "space" looking characters are not really spaces and don't get trimmed. I've searched around here, but can't find a definitive answer... What can I use in VBA to trim anything that isn't a-z or 0-9 from around data that's entered or pasted in?

View Replies!   View Related
Applying Trim To Cells With Hyperlinks
I have thousands of path names in a spreadsheet that were converted to hyperlinks, but my client wants to see only the filename in the hyperlink, not the whole path.

I created the hyperlinks by applying the following to the column containing the file paths: ....

View Replies!   View Related
Trim Formula: Separated By A Colon
I have been using the following formula's to trim some information that's separated by a colon, such as Pioneer Clinic:Dave that I have a column with Pioneer Clinic and a column with Dave McAfee:


However, now some of my data is separated by two colons! AVM:Pioneer Clinic:Dave McAfee. the formula for separating this so I still have two columns of Pioneer Clinic and Dave McAfee?

View Replies!   View Related
Trim Superfluous Spaces From Used Cells
I am working with a spreadsheet and rather new to be VBA. How do I select a range that only has data. I currently have the following macro, but when I run it, it checks every cell in the active worksheet which cause the application to hang. I would like it to automatically select only cells that have data in them ignoring all empty cells. I need this to be an automatically process running without the user selecting a range of data.

Here is my code ..

View Replies!   View Related
Trim Full Path To Filename Only
I have path data (subfolder depth varies) that I need to trim to filename only.


Need to end up with:

View Replies!   View Related
Trim Part Text To New Cell
I want to put text after firsr 15 chars to new row.

View Replies!   View Related
Trim Left And Minus A Character
Sub TrimSlashesToRight()
Dim cell As Range
For Each cell In Range("AN2:AN2000")
cell = Left(cell, InStr(cell, "//") -1)
End Sub

I cannot get it to run this, it always crashes on

cell = Left(cell, InStr(cell, "//") -1)

It will work fine like:

cell = Left(cell, InStr(cell, "//") + 0)

but then it leaves the first / in place, i want it to start deleteing before both //.

View Replies!   View Related
Concatenate Cells, Ignoring Blanks, Then Trim

I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.

View Replies!   View Related
Trim Leading And Trailing Spaces From Values
to trim leading and trailing spaces from values I'm getting from a For Next routine. I'm using c as my variable and Trim(c.value) as the operation.

There are other routines that search for the explicit string that's the result of the trimmed value and some can't be found because the trim as I'm using it isn't trimming.

View Replies!   View Related
Trim Month Year For Conditional Formatting
Having trouble with a date format:

I am trying to get CurrentDate from a 02/07/07 to 02/07

My CurrentDate = Format(CurrentDate, "mm/yy") doesn't seem to work, no matter what CurrentDate = mm/dd/yy

My eyes are watering from stareing at the screen for so long....

Private Sub TextBox24_Change()
Dim MonthPlus, CurrentDate As Date
Dim userow, usercolumn As String

View Replies!   View Related
Copyright Đ 2005-08, All rights reserved