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


Removing Blank Spaces

Am i able to remove blank spaces from cells retrospectively?

i.e. i have 1000 cells with names appearing in a cell thus " john smith" i want it to be "john smith".

so just removing the leading blank space only.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Removing Blank Spaces From The Beginning Of Cells
I'm compiling several old worksheets into a single database for a research study. The worksheets contain patient data. Some of the worksheets have the patient's last name, first name, and middle initial entered all in one cell like this: Smith,John R. Compounding the problem is the fact that sometimes the name is entered with a space between the comma and the first name, sometimes not.

I have formulas to break the name out into three separate columns "lName, "fName", "midInit". However, if the name in the original cell has a space between the coma and the first name, then the “fname” column will contain a blank space in front of the name. This is problem because patients names can appear in the database more than once. Some patients are in the database several times.

If patient “John R. Smith”, for example, is entered in the database as”

“Smith” “John” “R “

and also as

“Smith” “<space>John” “R”

then the database won’t recognize them as the same name when I search for John Smith’s data. Right?

If so, then I need a way to eliminate the empty spaces in front of the first names. Like I said, some have empty spaces and some don’t. I could do this by hand, but there are over 1000 entries in these worksheets.

View Replies!   View Related
Removing Spaces (?) From A Cell
I need to do a lookup on these cells but for some reason they all appear to have some kind of space characters in each cell which you can see if you double click on them. I have tried trims, text to columns, find replaces and none of these seem to work

I have a huge list of these numbers / codes and they all have the weird space kind of characters at the end

Can anyone give me a solution on how to remove them so my lookup will work?

View Replies!   View Related
Removing Spaces In A Cell
I have a column with 11 digit numbers formatted as text (Column C) and it seems like there's three spaces after the number (In Sheet A). I am trying to do a vlookup comparing Sheet A to Sheet B. It's not working and not sure if it's the extra spacing in the cell of Sheet A that's causing the Vlookup not to work.

I would like to compare both lists to see what UPC's are on both sheets. Any ideas how I can do this?

In Sheet A, Column C contains UPC (11 digits) 07845968952

In Sheet B, Column E contains UPC (11 digits)

View Replies!   View Related
Removing Mass Spaces
I have a file with thousands of records and on the name field, I have case like this:

1 "John Dough"
2 "John Dough "

As a result, when I pivot the data, I see duplicate lines for the same name. How can I eliminate the extra spaces at the end (row 2 and 3 above)?

View Replies!   View Related
Removing Spaces After Words
Spaces after entries keep messing up my VLOOKUPS. I get a lot of data from other people and when they have entered the information in some of the entries have a space after the word which mess up the results of my vlookups. Is there an easy way of going through and removing spaces after a word? Not all the words have spaces after them.

View Replies!   View Related
Removing Spaces After Semicolons
I have cells in a sheet containing varying numbers of items separated by semicolons. I want to convert these to columns using text to columns. The trouble is there are spaces after each semicolon, and I don't know how to get rid of them. I believe this may be compounded by the fact that the cells containing varying numbers of items. When I convert to columns I don't want each cell in the new columns to start with spaces. I'm attaching an example of what I mean (sanitized with fast food joint names).

View Replies!   View Related
Removing Spaces And Seperating Data
I have some data that is seperated by spaces. I need to split the data so each is in a seperate cell and then remove the spaces. I can do this if there is only one space inbetween the data, but that is not always the case. I attach a workbook with examples.

View Replies!   View Related
VBA - Removing Front Spaces And Special Characters
i have data which has lots of these hyphen "-" how can i get rid of them,

for example -Eq Cash-

i need the result to be

Eq Cash

Also i have lots of front spaces in my data, how can i get rid of those? so for example
Test (there is 2 spaces before T)

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
Filling Blank Spaces
I have blank spaces between data that I wanted to fill up with the same info as a group. the problem is it is not in a fix format.

For example 1
1_aaaa____data a____data a____data a
2_________data a____data a____data a
3_________data a____data a____Total
4_________data b____data b____data b
5_bbbb____data b____data b____data b
6_________data b____data b____Total

I need to fill A1 & A3 with aaaa and A4 & A6 with bbbb

Is there a way I can do that? the data is seperated only by the "Total"

View Replies!   View Related
Fill With Blank Spaces
I have a worksheet where the user input data and i have to make a macro that exports certain cell from the worksheet to a text file without any separating symbol, just like “CONCATENATE and EXPORT”. This is not hard to do, i’ve done it already. But the difficult part is here. My customer wants this macro to be interactive with the user and with an AS400 server that takes the textfile and reads it to find the value of certain variables, something like the opposite process of CONCATENATE. The hard part for me is that if any of the cells concatenated is blank i have to fill the text file with blank spaces.

If the concatenated cells are:
|one| two| three| tour| five|

the exported text to the text file will be:


But if one of the cells is blank:..................

View Replies!   View Related
Removing Blank Cells
I have a lists of e-mail address (up to 15 rows long) in adjacent columns.

At times one or more addresses need to be removed. After removal I need the list to 'bunch up' so that the space caused by the now blank cell is removed.

At the moment I am doing this with vba as follows:

Sub Clear_Blanks()

With Range("e13:e27")
. AutoFilter
.AutoFilter Field:=1, Criteria1:="<>"
.Copy Range("e35")
End With
Range("e35:e49").Cut Range("e13")

End Sub...

View Replies!   View Related
How Can I Replace Zeros With Blank Spaces During Calculations
I am trying to get excel to sum totals, but exclude answers that are outside
of a certain range. I can get it to do this by using =IF(Z8>0.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than a
zero because it alters future calculations.

View Replies!   View Related
Preventing Blank Spaces On Userform Textboxes
On a userform, I have three textboxes, one listbox, and an OK button. When the user presses the OK button, the information in these boxes is pasted onto a worksheet. I am wondering how I can detect if any of these boxes have been accidentally left empty, so as to prevent the user from pasting an incomplete entry onto the worksheet. This is what I have so far (this is part of the routine that executes when the OK button is pressed):

If EnterGrowerTextBox.Text = "" Then
MsgBox "Please Enter the Grower Name"
Exit Sub
End If
If EnterAmountTextBox.Text = "" Then
MsgBox "Please Enter the Number of Chicks"
Exit Sub
End If
If EnterBreedTextBox.Text = "" Then
MsgBox "Please Enter the Breed"
Exit Sub
End If......................

View Replies!   View Related
Removing Blank Rows On A Worksheet
I have to download a report through SAP and have written some pretty basic stuff to automate the formatting process, however, i still have to manually remove blank rows. I would like to be able to add the removal of theses rows as part of the code, one of the main problems that i see is that i run this report every week and the empty rows are not necessarilly in the order every time.

View Replies!   View Related
Removing Blank Lines From String
I have a texfile that populates a textbox on a userform. I would like to remove all blank lines in the string including those at the end if they exist, before populating the textbox. I'm reading the entire file at once into the string, not line by line.

Is there any way to edit the string called Text to remove the blank lines before populating the textbox? I'm looking for 2 carriage return characters in a row, and if so then remove one of them, but I don't know how to code that. This is in the userform activate section. If I read the textfile line by line, I don't know how to populate the textbox that way and remove the blank lines.

View Replies!   View Related
Removing Blank Entries That Show As Zero
I have a workbook containing several worksheets. I use one worksheet to collate information from the others. I do this by referencing the relevant cells I need from the other worksheets with the '=' command.

When this displays it shows as a '0' if the original cell is blank. Is it possible for this to show as a blank unless there is any data. I have tried the ISERROR function but it still leaves the entry as a '0'.

View Replies!   View Related
Shift Cells Up, Removing Blank
How do I shift all the cells up labelled data, so that there are no blank rows in between? I tried using the ones found on the forum via search but it is stuck in an infinite loop.

View Replies!   View Related
Removing Blank Cells In A Large Matrix
This is my first post on this forum and I am an Excel novice. I have a worksheet with lots of blank cells and I need to shift all the cells with data all the way to the left (to column A). I cannot get the sort or filter functions to do this for me, and I've never used macros but it appears they might be useful for my problem. I'd like to find an automatic process to move the data left rather than drag and drop hundreds of times. I'm attaching the complete xls file for your viewing.

View Replies!   View Related
Formula To Sort And Leave Spaces Blank Where One Column Doenst Have The Same Value
Every AM I run a report that has ALL of our company order numbers from the 2 systems we use. I get those reports and put them into 2 columns. instead of manually inserting so they all match up, is there a way to do this automatically? ....

View Replies!   View Related
Copy Column Of Data Removing Blank Cells
I have a long list of values in column F of my worksheet. These values appear in random rows for example: cell F3 is 27, cell F9 is 7, cell F13 is 27, cell F20 is 9 ... The data is not evenly spaced any specific number of rows apart, but there are spaces (rows which are blank in column F).

I would like to put all of these values in column G but without any blank cells, and keep the order the same as well. So using the above example, Id like a way to make cell G1 read 27, G2 read 7, G3 read 27, G4 read 9.

Column F could be a very long column im not sure exactly how long as the data is being put in periodically. Some sort of formula that I could just drag down that would read the values from column F and put them in column G with no places would be ideal.

View Replies!   View Related
Array Formula: How To Aggegrate Data, Removing Blank Cells
Lets say I have a column, and i have some dates in that column. between each date entry are blank cells, an undetermined ammount.

... etc ...

I would like some type of array formula, that could
agegrate that whole column into a new column, removing the blank cells.

so the new column would be:


I was thinking something like... but this doesnt get rid of the blanks...
(this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.


View Replies!   View Related
Sort Column To Match Pair In Other Column, Skip Blank Spaces
Here's what I have.. (on a much smaller scale)

(notice how the lines that match are now even.)

[Edited by admin~ *Link* to large images, don't display them]

View Replies!   View Related
How To Look For Spaces
I am creating a small function that should create a new login for future users. The login is 7 characters long. Spaces in that name (in cell A1 eg) should be removed? But how do I do this?

How do I remove the space in the name: "De Castro Imelda". Apparently the function TRIM does not help me much. Either I use the wrong function, or I my syntax is not good...

View Replies!   View Related
Imported Data Creates Blank Cells That Aren't Really Blank
Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C.

View Replies!   View Related
Count Blank Cells Within A Range Not Including Fully Blank Rows
I can count the blank cells withiin a range using


But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.

It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.

View Replies!   View Related
First Blank Cell In Column & Offset To 1st Blank In Row
I have checked archives, some similiar but not quite what I want.

In code below I want the output instead of going offset one column to right in same row to go one column to right into the next blank cell.

View Replies!   View Related
Add Spaces
I have a range of cells in column A... it's called SPACE. The code should be if the value in each cell is equal to "1" and select the NEXT row and INSERT an empty space.

Then keep going down the range to find the next "1", and add a space... but if it's not equal to one, keep going. Essentially I'm grouping data, with new data sets being indicated by the "1", and need to put an empty row in between the data sets.

View Replies!   View Related
Remove The Spaces
I am trying to remove spaces in front of a number (currently formatted as text)

I have tried "Text to Columns", "Trim", and other suggestions in previously threads.

I have copied bank statement amounts from an e-mail and the $ amounts have one Space in front of them. When I use the Trim function, and then a paste special, I still cannot get rid of the space, and so cannot add up the amounts in this column.


*7 Dec 2007*DEPOSIT*3,917.63
*7 Dec 2007*DEPOSIT*1,890.58

View Replies!   View Related
Want All The Spaces To Be Removed
I have a string which contain spaces in between. I want all the spaces to be remove.

Example: Say i have a string "IN DI A" and i want it to set as "INDIA".

Can some help me on this using VB.

I have 100 string to which spaces are to be removed and are to be kept in the same column.

View Replies!   View Related
Trimming Spaces ...
I've got a problem with a report that we need to manipulate in Excel. the report details the man hours each of our analysts has spent on calls and reports in the following format HH:MM:SS. However, the report comes out with a space after 'SS', so I need to trim this in order for excel to calculate total and average fix times. Cells are formatted to custom [H]:MM:SS.

With over 200 records requiring trimming per report, is there a formula that I can apply to this? I have tried =trim("Cell number") but this doesn't work.

View Replies!   View Related
Remove The Spaces...
I have a cel that has about 100,000 phone numbers and at the end of some and before sum, there are spaces added to end. is there a formula, or a way to take everything away except for the 10 digit number?

View Replies!   View Related
Removal Of Spaces
Assume cell A1 contains a set of words. If cell B1 contained the following formula, the contents of cell A1 will be shown in B1, minus the last word.

=If(ISNUMBER( FIND(" ",A1)),LEFT(A1, LOOKUP(32768,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))),A1)

However, the space or gap before the word that has been removed will be present in cell B1.

eg. If A1 = "how are you", then B1 will = "how are ".

I need this space to be removed too.
ie. B1 = "how are"

View Replies!   View Related
Spaces In Worksheets
I am trying to change values of some sheets in a worksheet, but the worksheet has spaces in the name. Is it possible to use that with spaces? I am also having the same problem where I am trying to copy some data between 2 different windows, and 1 of the file name has spaces in it, so I cannot use that either.

View Replies!   View Related
Trimming Cells (not Spaces)
Let's say in a cell i have the text ABCD/0123/01234 or ABC/0123/012345

How would i go about extracting whatever is inbetween the / ? i.e. 0123 in both examples?

View Replies!   View Related
Add Spaces To A Cell
I have columns that are defined with these width's:
A - 9B - 1C - 12D - 12

I need a macro that would add spaces to the right of the cell if the cell does not have number of characters of the lenght of the column width.

For instance:
Cell A1 has 5 characters so it would need 4 spaces.
Cell A2 has 7 characters so it would need 2 spaces.
Cell B1 has 1 character so it would NOT need a space.
Cell B2 has 0 characters so it would need 1 space.

View Replies!   View Related
Remove Trailing Spaces
I have written a function which works in the same way as the concatenate formula but where required it uses an underscore to make up the length (14 Characters) of the result. I seem however to have hit a minor snag when users input trailing spaces. I thought I could use trim to eliminate them but it doesnt seem to be working

Function HypCon(CorpAcct, Subdiv)

Dim n, i, iLen, iLen2 As Integer
Dim sCorpAcct, sSubdiv As String
Dim iLen3, iLen4 As Integer
iLen = Len(Trim(CorpAcct))
iLen2 = 7
iLen3 = Len(Trim(Subdiv))
iLen4 = 5..............

View Replies!   View Related
Remove Spaces And Stack
See attached. I want to use column A to create column B (the expected result) by way of a formula. Index?

View Replies!   View Related
Eliminating Spaces In Formula Value
I am having trouble with eliminating spaces from the value I've generated from my formula.

The formula will take the first 4 characters from a list of names, and add to that a number and a year. (For instance: "Conniff" would become "conn_01_09").

My problem is if I have "Ag Services". "ag s_01_09" would be generated. How can I eliminate that space? The list of names is in one field.

The formula I used so far is: =CONCATENATE(MID(E2,1,4),"_",MID(F2,1,2),"_",MID(C2,3,2))

I tried TRIM and CLEAN, but couldn't get them to work right.

View Replies!   View Related
Padding Cell With Spaces
I know how to pad cells with zeros for a numeric field. I can't figure out how to custom format a cell to pad a text field with spaces.

If I want description to have 10 characters, if they enter BLUE I want it to be BLUE then 5 spaces to make a total of 10) if they enter RED I want it to be RED with 7 spaces to make a total of 10.

With a number, I can just enter 00000 and if I enter 4, it will be 00004 Can you do this type of thing with a text? I can figure out a formula to get what I need, but I want a way to do it directly into the cell when the user types it in.

View Replies!   View Related
Remove Spaces From Numbers
I wrote numbers in column b cells in disordered way :

.. 1
.... 1
.. 1

I would like to make them to be aligned in the left side by a code.

View Replies!   View Related
Extra Spaces Before Keywords
I am calling a procedure from a commandbar button using on action.

With oButton
.Caption = "&Progress Report"
.OnAction = 'this bit please
.BeginGroup = True
.FaceId = 576
End With

Private Sub ProcessingRequest(ByVal shtRequest As String)

End Sub

View Replies!   View Related
Cannot Remove Trailing Spaces
I have an excel file with two sheets . One containing the updated prices with its code ( Sheet1) and the other containing the old prices with the same codes (sheet2). Now after several trials to copy the new prices from sheet 1 to sheet 2 with check if the code is same . ( I couldn't )

So how to get rid of the 20 spaces from Sheet1 column A.

View Replies!   View Related
Repeat Spaces In Msgbox
i'm writing to know whether in a messagebox like this:

View Replies!   View Related
Sum Last Six Numbers With Different Spaces
I have an instance where I have a list of numbers in different rows with different amounts and I want to add up the last 6 numbers, they're all in the same column however they're not all in consecutive rows.

Also, the gaps between the rows is not consistent.

The example below explains what I mean (I've add numbers in between so you can see the varying gaps, but they're actually blank cells)

(B1)ADD ME = 2
(B2)**blank cell**
(B3)**blank cell**
(B4)**blank cell**
(B5)**blank cell**
(B6)ADD ME = 5
(B7)**blank cell**
(B8)**blank cell**
(B9)**blank cell**
(B10)**blank cell**
(B11)**blank cell**
(B12)**blank cell**
(B13)**blank cell**
(B14)**blank cell**.........................

View Replies!   View Related
Deleting Spaces In A Column
In column A, there are 13000 plus entries mixed with General and Text Formats.:




the cell containing number starting with Zero is formatted as "text". The problem is that there are certain spaces given after some entries in certain cells. How those can be located and deleted.

View Replies!   View Related
Adde The Spaces To A String
I'm trying to speed up the creation of an input file that has a set character length but the inputs have variable lengths. For instance the identifier can have a string length of 7 or 8 depending upon the type.

BTW I've also tried using space(1) as opposed to " " in the loop.

View Replies!   View Related
Separate Alphabet From Spaces
I have data that I converted from txt. The problem is, upon conversion it has spaces at the end. For example:

"Monkey "
"Elephant "
"Birds "
What I need to do to remove the spaces?

View Replies!   View Related
VLookup :: Spaces In Data
i want to set vlookup but have spaces in the start of source and destination data is there any help for trailing this from vlookup. i have upload the sheet. i also tried vlookup("*"&.............) but not working

View Replies!   View Related
Superfluous Spaces In Cells
Public Function DSPACE(ByVal OCELL As String) As String
Dim sC As String
Dim i As Integer

For i = 1 To Len(OCELL)

sC = Mid(OCELL, i, 1)

If (sC >= "0" And sC <= "9") Or (sC >= "A" And sC <= "Z") Then
End If

Next i
End Function

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