Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How do I remove trailing blanks from numbers in order to use them in a formula?

## Similar Excel Video Tutorials

AVERAGE & Go To Blanks
- Excel! Learn how to use the Ctrl + Enter keyboard Trick to enter formulas or data into many cells simultaneously. Also, learn how the AVERAGE function ...

## Similar Topics

I use Excel 2003. I download transaction data from my broker and attempt, in vain, to sum a column of gain/loss numbers that contains two trailing blanks. The autosum works when I manually delete the blanks but the amount of data is large.

I would greatly appreciate help in deleting these two trailing blanks by using a feature of Excel.

Thank you,

blani.

Hi.

Does anyone know 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?

Thanks.

Friend

Hi All,

I am trying to automate the processing of data to a spreadsheet. I am copy and pasting the information into the spreadsheet and trying to write some vba to process it. The issue i have is that a lot of the data looks like it has trailing blanks (but there not!!!). I have used Trim and Clean but the data does not change, i have coded it correctly because i have used some sample data with trailing blanks and the code sorts it but it does nothing to the pasted data - any advice???

Thanks

Faulks

I'm trying to remove trailing commas with this forumula but for whatever reason it doesn't work. It does for letters though. Anyknow know how?

Here's the letter version

=LEFT(H2,LEN(H2)-1)&SUBSTITUTE(RIGHT(H2),"s","")

replaced the s with a "," and then replaced the "," with nothing to make.

=LEFT(H2,LEN(H2)-1)&SUBSTITUTE(RIGHT(H2),",""")

I'm just trying to remove trailing commas by way of formula. Any tips?

I need help creating a calculation that will do trailing stops on stock price.

Example: I buy a share for $10 and set a trailing stop of 25%.

If the stock drops to $7.50, the text changes conditionally to red

If the stock rises to $20, the new trailing stop is $15.

The trailing stop can go up, but never down.

Help!

Thank you.

Hi All In A1:A2 I have the following data:

ColumnA:

1987, 1988, 1989, 1990, 1991, 1992, 1993, 1994, (that is one leading blank at the start and one trailing blank after the last comma)

In A1, 2 things need to be done, the first leading blank needs to be removed and if the cell value ends in a comma, it should be replaced with a nothing ("") and any trailing blanks after it also removed.

that is initially A1 = " 1987, 1988, 1989, 1990, 1991, 1992, 1993, 1994, ", sfter the desired change A1 should = "1987, 1988, 1989, 1990, 1991, 1992, 1993, 1994" Notice the underlined sections are removed (and the leading blank).

How would I go about doing this without VBA.

Also are there any good references (besides from asking the gurus on this forum =)) that anyone would be able to direct me to for manipulating text based formulae?

I have seen a few posts that are close to this but not quite right. I have a situation where I need to do "=LEN..." formula that counts trailing zeros at the end of numbers (meaning zeros that are displayed at the end of a number but do not appear in the actual cell value).

example:

Cell E19 contains the actual value 4773.52, but it is displayed in currency format with FOUR digits so it displays as $4,773.5200.

The formula I am using is =LEN(MID(E19,FIND(".",E19)+1,4)). The result is 2, which means it does not include the last two trailing zeros in the LEN count.

Is there anyway to get it to include the trailing zeros so the result equals 4? Either w/formula, macro, or UDF?

Is weird I know, but I need to know how many decimal places (displayed, not actual) for many numbers on many sheets that I do not create myself. Any help would be great!

I know the Trim Function will remove leading and trailing blank spaces, but is there any similar function that will remove leading and trailing characters of my choosing? For example, if I want to remove leading or trailing commas, periods, dashes, etc...

When I use the filter to remove blanks, they disappear from my screen, but they still reappear sometimes and the numbers of the rows are still off. Once I've filtered out blanks, how do I permanently delete the rows with the blanks?

How I could plan a positive and negative trailing condition (trailing stop) on a stock value... thanks a lot of the answers

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?

Guys, how to remove trailing zeros from the following:

Column A

AB06182200

AB06223100

AB09786700

AB09786700

AB11701000

each record should only have 8 alpha numerics chars.

Hello I operate a fairly sophisticated excel spreadsheet with over 150 stock positions in it. I am trying to figure out how to build a formula for 25% trailing stops for all stocks.

For each security I have a column for the cost we paid for each and then a column with live feeds coming of current price.

Where I get confused is if a security is worth more than our purchase price I want to 25% trailing stop to increase with it but not retrace if the security falls. Here is an example which should help explain it.

Purchase company x for $10 a share.

First trailing stop is automatically generates a $7.5 stop price because we want a 25% trailing stop.

Now if security goes down 7.5 stays firm.

However, if security rises in value to say $15 the next day

I want trailing stop to automatically change to 11.25 15*.25=3.75 15-3.75=11.25

Then the third day say the security goes back to 11.50 I need my new stop price to stay firm at 11.25 and not retrace with the stock price.

So I get notified that if it hits my new stop price I sell.

Is it possible to automate and build a formula for all of that? Thanks for any help anyone has.

Hello I operate a fairly sophisticated excel spreadsheet with over 150 stock positions in it. I am trying to figure out how to build a formula for 25% trailing stops for all stocks.

For each security I have a column for the cost we paid for each and then a column with live feeds coming of current price.

Where I get confused is if a security is worth more than our purchase price I want to 25% trailing stop to increase with it but not retrace if the security falls. Here is an example which should help explain it.

Purchase company x for $10 a share.

First trailing stop is automatically generates a $7.5 stop price because we want a 25% trailing stop.

Now if security goes down 7.5 stays firm.

However, if security rises in value to say $15 the next day

I want trailing stop to automatically change to 11.25 15*.25=3.75 15-3.75=11.25

Then the third day say the security goes back to 11.50 I need my new stop price to stay firm at 11.25 and not retrace with the stock price.

So I get notified that if it hits my new stop price I sell.

Is it possible to automate and build a formula for all of that? Thanks for any help anyone has.

In Excel 2007, I have a spreadsheett that has numeric data for calcualtions. However I need to be ba able to convert this to text once all calcualtions are complete. If a number ends in zero the trailing zero is not converted to text since it is dropped in the formula bar.

How can I get the trailing zero to show in the formula bar and be able to have it as a numeric for calculations?

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

Thanks....I hope i made sense in my explanation!!

YG

Can anyone teach me how to do Trailing Stop losses in Excel for investments.

So when the price goes up the stop loss automatically adjusts upwards but if it falls then the trailing stop loss does not adjust down..

thanks so much

I'm trying to remove all trailing spaces from all cells in a spreadsheet.

I was going to apply a formula to one cell and then copy the formula to all cells. For example select the first cell and do =TRIM(A1). But I get the circular reference error. So I think the question is how to apply an in-place formula and put the resulting Value into the cell instead of the formula.

Or maybe the question is just, how do I remove all trailing spaces from all cells in a spreadsheet?Thanks,

Dave

I want a formula that will remove the trailing "EA" on each cell.Please note: The PINEAPPLES also contain EA and i don't want them removed because i will end up with a wrong data.

MELON SPANSPEK WHOLE EA

MELON WATER KG WHOLE EA

PINEAPPLE

PINEAPPLE

PAW PAW KG

PREPT FRUIT

APPLE FRESH PEELED DICED EA

APPLE GREEN UNPEELED SLICE

APPLE RED SLICE EA

GRAPEFRUIT SEGMENTS EA

LEMON WEDGES EA

Hi all

When I run a report off my local HR system the employee number 18 is printed off in excel.

However the number that I need is 000000000000018

How do I do this for a whole column of numbers ?

Another number 00000000000111 so is there 1 formula to capture this? The number basically needs to be 15 digits long including trailing zeros?

Thanks

I have a spreadsheet which contains numbers with lots of trailing digits after the decimal point, eg 15.87492105 and 0.618181818. How can I reduce the number of trailing digits, for example so that the first number would become 15.87 and the second would become 0.62?

Going to Format -> Cells and changing the number of decimal places doesn't work as it doesn't change the actual numbers, it only changes how the numbers are displayed within the cells on the screen. What I need is a method that changes the underlying number. Does anyone have any ideas how this can be achieved? Presumably it would be some kind of VBA procedure which would loop through all the affected cells.

I have imported data coming into excel with tons of blanks in front of the number. Is there a formula that will remove the blanks? If I paste it in a text file it comes across with "" so for example it looks like this "3434" I think it has something to do with formatting? I can't just format as a number or do text to coumns to remove the blanks.

Hello,

I pull in some data into Excel from a website and I then end up with a column similar to this:

Column I

French Opera 2/1

Chaninbar (FR) 33/1

Cornas (NZ) 15/2

Oiseau De Nuit (FR) 4/1

Tataniano (FR) 6/4F

Russian Flag (FR) 20/1

I would like to strip off the trailing letter if it exists. Sometimes one of the entries will have a trailing "F", as above. At other times one or more entries will have a trailing "C" or "J". The number of rows I have varies each time I pull in the data. Also, each of the above entries has a trailing blank space.

Could anyone help me to strip off the right-most letter if it exists in any of the rows, please?

Many thanks in advance.

Nick

k78=$608.60

len(k78)=5

i would like to see the trailing zero and the result would be a length of 6. i need this to do some other calculations

When I concactenate text and numbers together, the output

of trailing zeros behind a decimal is truncated. I need

the zero to remain. Excel 2002