Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I remove a blanck space from the beginning of a formula?
I have a leading space in my formula, which is causing it not to function.
How do I get rid of it? |
#2
|
|||
|
|||
Hi
select the formulas and choose edit / replace in the find what box press your space bar leave the replace with box empty press Replace All NOTE: when trying something new like this it is always a good idea to do it on a copy of your workbook first ... just to ensure that the results you get are the results you expect. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "kan2953" wrote in message ... I have a leading space in my formula, which is causing it not to function. How do I get rid of it? |
#3
|
|||
|
|||
Hi,
Not sure what you mean by a space at the beginning of a formula. To remove a leading space from the contents of a cell, use the TRIM function. If A1 contains (space)ABC, then, if you enter =TRIM(A1) in cell B2, the contents of B2 will be ABC. "kan2953" wrote: I have a leading space in my formula, which is causing it not to function. How do I get rid of it? |
#4
|
|||
|
|||
If you have a space before the equal sign in the formula
that you want to eliminate so the formula can function you can use the TrimALL macro found on my page http://www.mvps.org/dmcritchie/excel/join.htm#trimall --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "kan2953" wrote in message ... I have a leading space in my formula, which is causing it not to function. How do I get rid of it? |
#5
|
|||
|
|||
Would be okay if the only spaces are unwanted
spaces before the the equal sign, but it would be better to replace space equal sign by equal sign. so as not to destroy embedded spaces as in i.e. = A1 & " " & B1 The TrimAll macro on my join.htm page will convert CHAR(128) to spaces first and then TRIM. Used all the time as a fixup for bad typing introducing spaces and for copying tables from web pages where the spaces are really (nonbreaking spaces). --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JulieD" wrote in message ... Hi select the formulas and choose edit / replace in the find what box press your space bar leave the replace with box empty press Replace All NOTE: when trying something new like this it is always a good idea to do it on a copy of your workbook first ... just to ensure that the results you get are the results you expect. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "kan2953" wrote in message ... I have a leading space in my formula, which is causing it not to function. How do I get rid of it? |
#6
|
|||
|
|||
Hi Ken,
If you use the TRIM Worksheet Function, you will remove the leading space, but it will be your TRIM formula that is in use and it will be text. Converting the formula to values would have a text value that looks like a formula, you would then have to hit F2 and Enter for each cell or run a macro that does reenters Better solution, I think, is to use the TRIMALL macro in http://www.mvps.org/dmcritchie/excel/join.htm#trimall and change the cells right where they are. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ken Hudson" wrote in message ... Hi, Not sure what you mean by a space at the beginning of a formula. To remove a leading space from the contents of a cell, use the TRIM function. If A1 contains (space)ABC, then, if you enter =TRIM(A1) in cell B2, the contents of B2 will be ABC. "kan2953" wrote: I have a leading space in my formula, which is causing it not to function. How do I get rid of it? |
#7
|
|||
|
|||
Hi David
you're absolutely correct ... don't know where my brain was when i typed that answer:) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "David McRitchie" wrote in message ... Would be okay if the only spaces are unwanted spaces before the the equal sign, but it would be better to replace space equal sign by equal sign. so as not to destroy embedded spaces as in i.e. = A1 & " " & B1 The TrimAll macro on my join.htm page will convert CHAR(128) to spaces first and then TRIM. Used all the time as a fixup for bad typing introducing spaces and for copying tables from web pages where the spaces are really (nonbreaking spaces). --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JulieD" wrote in message ... Hi select the formulas and choose edit / replace in the find what box press your space bar leave the replace with box empty press Replace All NOTE: when trying something new like this it is always a good idea to do it on a copy of your workbook first ... just to ensure that the results you get are the results you expect. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "kan2953" wrote in message ... I have a leading space in my formula, which is causing it not to function. How do I get rid of it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove spaces in text in excel | Excel Discussion (Misc queries) | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) | |||
Removing a space within a cell | Excel Worksheet Functions |