 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2007how do I remove leading spaces and leave the remianing spaces w
I am attempting to remove any leading spaces should there be any within a range of cells Example: In Cell B16 " find the spaces" in Cell B17 " Must leave other spaces" In Cell B18 "May have no leading spaces" The only spaces I would want to eliminate are the ones before "find...." and "Must.." Try the TRIM function: With B16: " find the spaces" C16: =TRIM(B16) Returns "find the spaces"....without the quotes, of course. Does that help? *********** Regards, Ron XL2002, WinXP > I am attempting to remove any leading spaces should there be any within a > range of cells [quoted text clipped - 6 lines] > The only spaces I would want to eliminate are the ones before "find...." and > "Must.." This worked however it return the data to the cell the formua is in. It would work if there was a small amount of data to correct however I have a large listing to remove intermittal leading blanks. My desire results is to have the range of data corrected remaining in their original cells. Is this possible? > Try the TRIM function: > [quoted text clipped - 21 lines] > > The only spaces I would want to eliminate are the ones before "find...." and > > "Must.." Debi <D...@discussions.microsoft.com> wrote... >I am attempting to remove any leading spaces should there be any >within a range of cells [quoted text clipped - 6 lines] >The only spaces I would want to eliminate are the ones before >"find...." and "Must.." With formulas, =REPLACE(B16,1,FIND(LEFT(TRIM(B16),2),B16)-1,"") This will remove only the leading spaces, not trailing spaces or sequences of multiple spaces within the text. With menu commands, select B16:B18, run Data > Text to Columns, select Fixed Width, click the Next button, remove *ALL* the break lines by double clicking on them, then click the Finish button. This is a useful Excel quirk when parsing single fields. Thank you I used the option from the Data Menu and it works perfectly providing the exact results I want. > Debi <D...@discussions.microsoft.com> wrote... > >I am attempting to remove any leading spaces should there be any [quoted text clipped - 19 lines] > double clicking on them, then click the Finish button. This is a > useful Excel quirk when parsing single fields. While the option from the data menu worked could you please explain How the Replace function works as well as what each peice of the formula represents. > Debi <D...@discussions.microsoft.com> wrote... > >I am attempting to remove any leading spaces should there be any [quoted text clipped - 19 lines] > double clicking on them, then click the Finish button. This is a > useful Excel quirk when parsing single fields. Data|text to columns will remove trailing spaces, too. (Doesn't sound like a problem for the OP, though.) > Debi <D...@discussions.microsoft.com> wrote... > >I am attempting to remove any leading spaces should there be any [quoted text clipped - 19 lines] > double clicking on them, then click the Finish button. This is a > useful Excel quirk when parsing single fields.  SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.