Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
kan2953
 
Posts: n/a
Default 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   Report Post  
JulieD
 
Posts: n/a
Default

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   Report Post  
Ken Hudson
 
Posts: n/a
Default

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   Report Post  
David McRitchie
 
Posts: n/a
Default

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   Report Post  
David McRitchie
 
Posts: n/a
Default

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   Report Post  
David McRitchie
 
Posts: n/a
Default

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   Report Post  
JulieD
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
remove spaces in text in excel GnarlyCar Excel Discussion (Misc queries) 3 February 1st 05 06:02 PM
Remove link fr a column of entries Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 18th 04 11:51 PM
Remove link fr a column of entries Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 17th 04 09:07 PM
Remove link fr a column of entries Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 17th 04 07:44 PM
Removing a space within a cell JERRY8 Excel Worksheet Functions 3 December 2nd 04 04:22 AM


All times are GMT +1. The time now is 07:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"