+ Reply to Thread
Results 1 to 5 of 5

leading space in a cell not getting removed with trim function

  1. #1
    Forum Contributor
    Join Date
    02-11-2008
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    242

    leading space in a cell not getting removed with trim function

    Hi,
    I have this file which has leading spaces in column R of H1 sheet,I have used trim function and then converted it to number,but still it is not working,due to that the value in pivot is also showing '0'
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: leading space in a cell not getting removed with trim function

    You need to take PO Line Balance Qty and not PO Line Amount Balance.

    PO Line Amount Balance has space at the end and can be removed with SUBSTITUTE function.

  3. #3
    Forum Contributor
    Join Date
    02-11-2008
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    242

    Re: leading space in a cell not getting removed with trim function

    Hi,
    Sorry for the wrong communication I want to remove space from PO line amount balance column where in certain places there are 1 space and in some cells more than 1,can you you guide me as to how to use the substitute function .

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: leading space in a cell not getting removed with trim function

    Try =SUBSTITUTE(A1, " ", "")
    Change A1 is your desired cell.

    However, this will return you a text. (So 5 03 will become 503 (a text value).

    If you expecting number use: =--SUBSTITUTE(A1, " ", "") but this will return you error for text values (so 5A03 will become #VALUE!)

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: leading space in a cell not getting removed with trim function

    Try this:
    Please Login or Register  to view this content.
    Confirmed with Crl-Shift-Enter
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1