+ Reply to Thread
Results 1 to 3 of 3

Trying to remove a space before a number in a cell

  1. #1
    Registered User
    Join Date
    12-01-2018
    Location
    TBC
    MS-Off Ver
    MS 2013
    Posts
    2

    Trying to remove a space before a number in a cell

    Hi Folks

    I have a list of numbers and i need to do a lookup into another sheet.

    The lookup is working for the most part except for those numbers in the sheet I am looking up into that seem to have a space before them.

    I tried the TRIM function but it does not work.

    Maybe it is due the format of the numbers or because it looks like they were pasted in from elsewhere. The format of the cell category is General.

    I have attached the file

    Any ideas?
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,232

    Re: Trying to remove a space before a number in a cell

    The "space" is actually the non-breaking space character, with a code of 160 instead of the usual 32. This can be removed from all the cells in one operation using Find & Replace (CTRL-H). Highlight all the cells in the column, then CTRL-H, and:

    Find What: Alt-0160

    Replace With: leave empty

    Then click on Replace All

    where Alt-0160 mean to hold down the Alt key and type 0160 on the numeric keypad. Alternatively, you can copy that space from one of the affected cells.

    Hope this helps.

    Pete

  3. #3
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Trying to remove a space before a number in a cell

    Your text has a character 160 in first position. Try

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Better solution is proposed by Pete
    Last edited by Josť Augusto; 12-01-2018 at 11:08 AM. Reason: Add note

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to remove space between number and replace with period
    By navids in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-30-2017, 09:45 AM
  2. [SOLVED] remove space between number and english in one cell
    By hktom in forum Excel Formulas & Functions
    Replies: 56
    Last Post: 12-29-2015, 03:54 PM
  3. [SOLVED] To Remove Space in number
    By ganeshkumar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-01-2013, 06:01 AM
  4. Replies: 1
    Last Post: 09-05-2013, 11:26 AM
  5. [SOLVED] If Cell Starts with a Space, remove that space
    By Ocean Zhang in forum Excel General
    Replies: 2
    Last Post: 09-29-2012, 01:52 PM
  6. Remove trailing space from a cell, convert to number
    By lil_ern63 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-20-2010, 12:19 PM
  7. Replies: 6
    Last Post: 06-29-2006, 01:20 PM

Tags for this Thread

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