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


Advertisements:










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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Padding Numeric Values With 0's
I need a left side padding for numeric values to keep them in 4 digits
ie 1 becomes 0001, 25 becomes 0025 and 345 becomes 0345. Following is the custom number format I tried and it fails.

[<10]"000"#;[<100]"00"#;[<1000]"0"#


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
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
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
Delete Empty Spaces In Cell
How do i delete an empy spaces in cell. For example i have this value:
33.1 x 2.9 and i want to have: 33.1x2.9

View Replies!   View Related
Add Same Number Of Spaces As Cell Value
I have a column with a possible value of 1 to 7. The value represents the day of the week. I would like the value to be displayed in such a way that it is on the right position in relation to other days. So day one is a 1 at the first position, day 2 will be a space and then a 2, day 3 will be 2 spaces and then a 3 etc etc.

View Replies!   View Related
Spaces Found In Cell V Copied Off Webpage
I copied some data off the web and there are a ton of cells with Currencies that I would like to take out the spaces infront of the numbers. Because without doing so, I am unable to use them in formulas.

so I would like to convert this ' $52,000' to '$52,000'

I tried the Trim(Cell) but that doesn't seem to work, this data does not appear to be text. I can't even change the format on it, doesn't make a difference.

View Replies!   View Related
How To Remove Spaces And Combine 2 Cell Data To One
cell A1 has " HH001 " (without "", there is space infront and behind the data),

cell B1 has "motor gear case",

what formula to put in cell C1 to get like this "HH001" (removed spaces in A1)

and D1 to get like this, "[HH001] motor gear case"

View Replies!   View Related
Copy, Paste Cell With Spaces But Keep Integrity
I am trying to copy form sheet1!A1 into sheet2!A1.

Now, I want the data in sheet2!A8 to be what is in sheet1!A2 in the same order. So sheet2!A15 will have data from sheet1!A3 and so forth.

View Replies!   View Related
Strip Excess Spaces Out Of Address Cell
I need VBA to remove more than 3 blank spaces after the city & replace them with a comma & one blank. For example:

All in one column AB starting with row 7 I have:

Johnson City (13 Blank Spaces) NC 75503 (1 to 3 spaces after state & is OK)
Jacksonville (10 blank spaces) Fl 23854
Henderson (12 blank spaces) NH 28363 (12 blank spaces)
etc

I need all in one column AC starting with row 7:
Johnson City, NC 75503 (,space before state, same space after state ok)
Jacksonville, FL 23854
Henderson, NH 28363
etc

View Replies!   View Related
Validation Formula To Prevent Spaces In Cell
I am in need of 2 validation macros (formulas) to enter into the validation criteria formula box when creating a custom validation.

1. A formula that will generate an error if there is a space in the text entry. Example:060107_Halo3CG prev.mpg would be an invalid entry due to the space after CG.

2. The same formula as above but this one also needs a 50 character limit added to the criteria.

View Replies!   View Related
Add Specific Number Of Spaces To Text In Cell
I've got a single column worksheet with a varying numbers of characters in each row.

At the end of each row's cell value I must add a 5 char string. Preceding that string I must have enough spaces to make the total length of each row 106 characters.

I've already:Defined and populated the string. Let's call it "strMyString".Established how many rows are in the sheet and stored it in "lngUsedRange"Written the following which cycles through each row establishing how many spaces need to be added:

Dim lngSpacesNeeded As Long

For i = 1 To lngUsedRange
lngSpacesNeeded = 101 - Len(Range("A" & i))
Next i

Now I just need to know what else to put before "Next i" to locate the end of the existing text in the cell and add the number of spaces in "lngSpacesNeeded" then add "strMyString".

View Replies!   View Related
Copy The Last 10 Spaces Of A Cell To Another Cell
i have a text field that has last name, first name and an id number. All of this data is in the same cell. i want to take the id number from this cell, and paste it into an adjacent cell. The id number takes up the last 9 or 10 spaces in the cell.

View Replies!   View Related
Check If Text Typed In Cell Is Repeated In Row, Discounting Spaces
is there a formula which can locate instances of same text even though due to spaces it may look different? for example, "bad apple" and "badapple" are essentially the same.

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?

Sub TRIM_RANGE()
Dim myRange As Range
Dim myRow As Range
Sheets("CAMPAIGNS_2007").Select
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
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
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.

Ex:

*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
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
___A_______B________C________D
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
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
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
Remove Spaces From Numbers
I wrote numbers in column b cells in disordered way :

1
.. 1
1
.... 1
.. 1
1

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


View Replies!   View Related
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 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.:

12234

1223

01234

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
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
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
DSPACE = DSPACE + sC
End If

Next i
End Function

View Replies!   View Related
Add Comments, No Spaces
I have a sub that works just fine, but I need to test it for blanks.

Sub AddToComment()
Dim rCell As Range
Dim cCom As Comment
Sheets("Support Detail").Select
With Range("e5")
.ClearComments
Set cCom = .AddComment
End With
Sheets("Sheet1").Select
For Each rCell In Range("a4:a23")
cCom.Text Text:=rCell.Text & " " & cCom.Text
Next rCell
End Sub

I only have 1 line of test in the a4:a23 range - so I'm getting 19 spaces before the text in the comment. If the cells within a4:a23 are blank, how do I ignore them? I'm not sure how to interpret this code, so I'm sure it's probably easy:

cCom.Text Text:=rCell.Text & " " & cCom.Text

View Replies!   View Related
Spaces In Path And Filename
I have a problem with paths and filenames with spaces.
If the the excel file containing the macro file is opened from explorer rather than from within excel, the path is incorrect. Therefore I use the following

Dim varPath As String
varPath = ThisWorkbook.Path
ChDir varPath

I can then later call either varPath or the VBA GetCurDir and it will be correctly set.

However, when I call a Shell function as in the example:

Dim varPath As String
Dim fPath As String
varPath = ThisWorkbook.Path
ChDir varPath
fPath = varPath & " emp"
MsgBox fPath & "test.bat"
Shell fPath & "test.bat"

I get the message box: e:local data tables emp est.bat
I get the error: Runtime Error 5, Invalid procedure call or argument.

Without having to rename the path structure so that it doesn't have spaces (which isn't always possible), how can I get this to work?

View Replies!   View Related
Remove Spaces From Text
I'm trying to use the SUBSTITUTE function to remove spaces from my cells in column A. The trouble is My formula is removing all the spaces from the cell. My formula is: SUBSTITUTE(A1," ","")

I do however have many cells that contain a space between characters 5 and 7 and spaces at the end of the cell which have been padded out to 15 characters long.Its only the spaces at the end of the cells I need to remove.

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:

onetwothreefourfive

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

View Replies!   View Related
Running A Userform, Name With Spaces
I ran into what looks like to be a syntax error.

Application.Run " Name with Spaces!Run_frmMacroList"

does not work BUT.... when i take the same file and "save as" Book1

Application.Run "Book1!Run_frmMacroList"

works perfectly fine.

View Replies!   View Related
Remove Superfluous Spaces
I convert a .pdf report to excel using Able2Extract. The output is leaving some xtra spaces inside the word. For example,

Clean in g Co st

I tried running Dave McRitchie's Trimall on the text, Sample workbook attached

View Replies!   View Related
Remove Spaces From Selection
I have a macro that I run after selecting a column which removes spaces. However, it processes all 65536 rows and I only want it to process the cells with values. I need to enter into an input a column and then it just removes spaces from the cells with values

Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
c = Replace(c, " ", "")
Next
End Sub

View Replies!   View Related
Eliminating Spaces Within Text Cells
I have a formula that is dependent upon a column of cells containing text. Cells within this column randomly have an additional space (" ") following the words. With this invisible space, the formula doesn't work as intended.

Since I have an extremely long list of names, is there a way to easily remove additional spaces after words without manually going through each cell and deleting them?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved