Delete Leading And Trailing Periods
Jun 4, 2009
I have a spreadsheet with numbers of various lengths.
Sometimes they are preceded with a period and have one at the end as well.
Examples: of their present state
.11110000.111111.000000.
111111.1111111.000000.
100000.000000.0000.110001.
Examples: of desired state
10000.111111.000000
111111.1111111.000000
100000.000000.0000.110001
Is their a way I can create a new column and insert a formula, then drag it down so that it will delete the first or last character, if the character is a period?
View 6 Replies
ADVERTISEMENT
Dec 5, 2013
Any easy way to remove all spaces from a cell, both leading and trailing? I find it hard to believe that Excel doesn't have this functionality. I don't particulary want to write a VBA script since I have never done it but if that's the only way, I'd love to know how to write it. I have looked everywhere but obviously not in the right places.
View 4 Replies
View Related
Feb 13, 2009
I asked this along time ago and received a worksheet formula which suited my needs then. http://www.excelforum.com/excel-work...trailing+zeros
Now I need this in a macro. Below is my current concatenating code.
View 3 Replies
View Related
Mar 31, 2009
I am using the following code to import an excel file into an existing sheet. The problem I am having is the way some of the cells are being imported. Some of the cells have spaces before and after the number.
I need to delete the leading and trailing spaces for each cell affected. Is there a code I can incorporate to do this? BTW…the columns effected are columns F & G.
View 8 Replies
View Related
Jun 10, 2008
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 9 Replies
View Related
Sep 1, 2008
I wrote a tool that people at work use. They initially need to paste in a bunch of customer locations with Address, City, State, Zip, etc. Sometimes the Users have "bad input" data that has non-breaking spaces, multiple space between words, or leading and trailing spaces and nonbreaking spaces. I have code to get rid of all of those problems. However, the Users often use their data for other important functions at work. So I want to give them a message to let them know that their Original Data is "bad".
So instead of just "Fix" . . . I want to "Report the problem", then "Fix". I need to identify exactly what problem was found - not just tell the User that their data is bad.
I wrote a simple Search routine with error handling that identifies 2 of the 4 cases and notifies the User:
Case 1) ASCII 160 (non-breaking space, HTML  
Case 2) multiple spaces (2 or more consecutive spaces)
Case 3) Leading or Trailing Spaces (ASCII 032)
Case 4 Leading or Trailing non-breaking spaces (ASCII 160, which is HTML  )
I cannot quite figure out how to find the 3rd and 4th Cases. If anyone can help me with Case 4 especially, then I can probably do the same thing for Case 3.
I think it will work to somehow use this idea - the code is not even real code but it is just conceptual:
RIGHT(CellReference, 1) = Char(160) or Char(032)
LEFT(CellReference, 1) = Char(160) or Char(032)
Anyway, here is what I have so far . . .
Sub NotifyBadInput
ErrorFlag = False
Cells.Select ' select entire worksheet
' BAD INPUT 1 - lLook for any occurence of ASCII 160 (non-breaking space, HTML  )
' and Notify the User if any of his Input cells contain  's
On Error Goto errormsg1
Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
View 9 Replies
View Related
May 27, 2008
While entering data space is given in the first and last of each cell content. For example
if there is a word Alex Patrix in a cell, space is given before A of alex and after x of Patrix. This is done fo many cells. I want to remove only initial and last space which is un-necessary.
The space caused problem to compare cell so i've to remove space.
I've around 2500 cells with this problem.
View 2 Replies
View Related
Jun 23, 2014
I want to alert a user that he or she has typed in a text string that begins or ends with an apostrophe ' or begins or ends with a quotation mark " so that they can take corrective action in removing these characters. The reasons for this are superflous to this post but what I thought was a logical answer returns a formula error. Here's the scenario:-
A user enters a string of text in cell A1.
Cell B1 checks if the text, if any, in A1 begins or ends with a ' or a " and if it does, returns an "Error" message
The formula I've tried in B1 is =IF(OR(LEFT(A1,1)="'",LEFT(A1,1)=""",RIGHT(A1,1)="'",RIGHT(A1,1)=""")),"Error","Ok") but Excel will have none of it.
View 9 Replies
View Related
Jan 28, 2013
I have 4 columns (and a couple of other text columns) that are formatted as decimal with 6 dicimal points.
I need to create a text file with lines that are 80 bytes each from the spreadsheet - no spaces inbetween each of the characters.
The issue I am having is trying to format the 4 decimal point columns to be fixed numbers and retain the leading or trailing zeros to do a concatenation with the other columns.
Just an example of some what some of the values look like on my spreadsheet:
.123456 | .123450 | .012345 | .012340
Essentially when I'm all said and done, I would like the row to look like "text123456123450012345012340text"
I tried to format 4 new columns as general and use a "=right(cell,6)" and that worked pretty well for the cells that have leading zeros, but for the cells that have trailing zeros, it doesn't seem to pick up the zero. I tried to use a "=mid(cell,2,6)" but that didn't work either.
View 5 Replies
View Related
Dec 29, 2006
I am having trouble writing a Macro to delete multiple periods at the end of each cell. Each cell looks simiar to this: Blah Blah Blah................... I am using the InStr function to find the first period but it returns a 0 everytime. Here is the code I used to find the period. I want it to delete everything after the first period and store the value back in the same cell. Right now it just stores blanks.
Sub PeriodRemover()
Dim NewContract As String, Contract As String
Application.Calculation = xlManual
i = 2
Sheets("Product Listing by Contract").Select
Contract = Range("A" & i).Value
Do Until Contract = Empty
NewContract = Left(Contract, InStr(Contract, "."))
Range("A" & i).Value = NewContract
i = i + 1
Contract = Range("A" & i).Value
Loop
Application.Calculation = xlAutomatic
End Sub
View 4 Replies
View Related
Feb 4, 2010
I got the following table.
http://img163.imageshack.us/img163/2972/oldnf.jpg
What formula could I use so that the amount under STP premium would display at the right cell under different time periods depending on the time of the trade date, just like the following images?
http://img687.imageshack.us/img687/5720/newcg.jpg
http://img168.imageshack.us/img168/413/new1zk.jpg
View 2 Replies
View Related
Jun 20, 2014
I'm using this formula (below) to add the hrs between 2 time periods (4:30 pm in cell I37 and 8:00 pm in I38, then the formula is in I39) and works almost every time even when the second time goes into the am hours, except in certain instances such as: 4:30 pm to 11pm show as 30.5? But sometimes it shows the correct number, 6.5
=(IF(I37>I38,I38+1-I37,I38-I37))*24
View 1 Replies
View Related
Oct 7, 2008
I have been working on solving this riddle since yesterday. What I'm trying to do is have Excel use today’s date on the computer to tell me when my Paydays are in a given month. Or have it tell me the next two or three Paydays based on Today's Date.
I can get Excel to calculate Every Two Weeks by using the formula =A3 + 14 then have each sequential field add 14 days to the previous fields date. However what I end up with is just a column of Pay Dates. Not what I'm looking for. I want to have an active page that only shows 2 or 3 future Pay Days. I want to use the function =TODAY() in my formula so that every time I open up the file it can calculate the next Pay Dates automatically.
I have been playing around with this formula:.....
All this does is tells me the First Friday of every month. This won't work for me because the Pay Periods are separated by 14 days not the 2nd and 4th Friday of every month. Here is an example of my Pay Periods: Sep 19, 2008; Oct 3, 2008; Oct 17, 2008; Oct 31, 2008; Nov 14, 2008; Nov 28, 2008; Dec 12, 2008; Dec 26, 2008; etc.......
View 5 Replies
View Related
Feb 10, 2010
I'm trying to calculate the total hours worked for two given periods over a shift , which can span two consecutive days ie. start 15:45 and finish at 00:15 the next day. Hours worked between 6am and 6pm are paid at standard rate, whilst hours worked between 6pm and 6am attract penalty rates. Hours are cacluated in 24hr time
I have attached a copy of the timesheet that we use so you can see exactly what I'm trying to achieve, and included most of the shifts that we have.
View 14 Replies
View Related
Nov 23, 2011
When using the Trend function, I can get the value for individual future periods. I then can get a total for the year by summing each individual future period.
What I don't know how to do is sum them all using one formula. For instance, If I know the values of periods 1-5, how would I write a formula that would sum the future values (in one cell) of periods 6-12 -- without having to sum each future period individually?
View 3 Replies
View Related
Aug 5, 2013
I have a list of email addresses with the periods stripped before the .com, .net, etc.
How to insert the period to go from example@mailcom to example@mail.com? Obviously the email addresses are of different lengths, but it's always 3 characters after the inserted period.
View 3 Replies
View Related
Apr 22, 2008
I have a text value which consists of two periods ".". I want to pull the value in the middle. eg. From the text value in a cell "oakland.california.usa", I want to pull "california" in another cell. All the values in column are in similar format.
A B
1 oakland.california.usa california
Hope this is not duplication of any pre-existing thread, if it is, I probably don't know the correct keywords to query.
View 2 Replies
View Related
Jul 9, 2009
how to set the format of a cell such that it will automatically add trailing blanks to the data entered into that cell while fixing the number of characters that could be entered into that cell?
View 3 Replies
View Related
Jan 13, 2010
Now I have a decimal column and I would like to remove all of the trailing zeros. It is using the number format. How do I accomplish this?
View 10 Replies
View Related
Sep 18, 2013
I have a column of data in which each cell contains a line of text. Each line of text has spaces after the letters end and there doesn't seem to be a consistent number of trailing spaces.
I can clean up the data using the TRIM function but is it possible to use the TEXT TO COLUMNS functionality?
View 2 Replies
View Related
Nov 23, 2007
I would like to use the following code to produce a message with two numbers in it, both showing an exact golf handicap to one decimal point. If a number is exactly 6 I want it to show as 6.0.
All works well for the number I'm collecting from the user and storing in newh. But I can't retain/produce the trailing zero from oldh which is formatted in the spreadsheet as Custom 0.0.
View 10 Replies
View Related
Dec 29, 2009
I need a macro to do a comparison between two spreadsheets, and one part of it is throwing me, as I need to compare apples to oranges. Here's the situation:
Sheet A has the data noted like this:
PAC
11000
11100
11500, etc.
All data should have 5 digits, and my numbers range from 11000 to 87028, though quite a few are skipped.
Sheet B has data like this:
PAC
110
110-CBS
11000
11100-ING
112
11500-L
How can I change sheet B to:
1) Drop off the dash and anything following?
2) Add trailing zeroes to make sure all numbers are 5 characters in length?
View 3 Replies
View Related
Jul 30, 2008
What the quickest way to get rid of trailing spaces from a column of imported data ?
View 12 Replies
View Related
Oct 12, 2008
I have a set of data with trailing Y in certain cells.
Instead of the replace function in Excel, is there another way of removing the "Y" in that cell and storing it another cell while keeping the numbers before the Y.
i've created another column to determine which cells has Y as the right most character by using =IF(RIGHT(F2,1)="Y","Y","")
So for example, I want to remove the Y from say Cell A2 and store it in say cell A3 while still keeping the value 8624.46 in A2
Cell A2 = 8624.46Y
View 9 Replies
View Related
Sep 24, 2006
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 9 Replies
View Related
Oct 29, 2006
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 9 Replies
View Related
Mar 15, 2008
Removing Trailing Commas
I have a list (general ledger) GL codes with trailing commas
Example:
123456789,123456722,123789456,,,,,
When I try to use “replace” and replace the multiple commas with nothing it turns my gl codes into a scientific number (1.23456789123456E+26), but my cell is formatted to text.
The other problem is for numbers with a single trailing comma, it would also remove the commas between the GL codes
Is there a function that removes trailing commas, or commas that are not followed by numbers?
View 9 Replies
View Related
Feb 14, 2014
Trying to calculate the lost revenue value for when a piece of machinary is offline for a period of time based on the hrly price during that particular period. The machines come on/off line at various times during the year so i am trying to make this a bit dynamic so i only need to add new info and not have to change the formula
Column F - Off date of machine
Column G - Restart date of machine
Column H - Hrly time
Column I - Hrly price
I thought this formula (an array) would work for me
=average(if((Column H>=Column F) * (Column H<= Column G), Column I)) but it only calculates that average price for the first period and not new average prices for other periods.
what adjustments I can make to my formula or is there a better formula to use?
View 1 Replies
View Related
Mar 20, 2009
I am trying to find a way to identify the nmber of periods where a particular value is exceeded.
Basically I have daily data on sales for 40 years and I would like to define a level of sales i.e. 23 units per day and a period i.e. 10 days and then output the number of times where the recorded sales level is greater than 23 units per day for 10 or more days.
Ideally I would like it so the sales level and period can be set in reference cells and the formula can adapt to different sales levels and periods.
The data is formatted as follows: ...
View 7 Replies
View Related
Jul 5, 2009
How can days be allocated into the periods in which they fall if they run across a number of months.
The attached sample might explain it better.
View 2 Replies
View Related
