+ Reply to Thread
Results 1 to 3 of 3

Removing Spaces from text

  1. #1
    Registered User
    Join Date
    11-21-2011
    Location
    Sussex, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Removing Spaces from text

    Hi,

    I've got a list of postcodes that I need to use in another formula, but there are spaces in odd places within the data that I need to remove. Is there a way of doing this?

    Example:

    NN 7 3AP
    NN 4 7AA
    NN 3 6AH
    LU 4 9UJ
    NN 3 6LR
    NN 3 3JB
    NN 2 7HG

    I need to not have the space between the NN and the 7, but I'm happy to take out all spaces in the cell if that's easier.

    Thanks!

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

    Re: Removing Spaces from text

    If you don't mind losing all the spaces, then you can do that in one operation using Find & Replace. Highlight all the data, then CTRL-H, and Find box (type a space), Replace box (leave blank), click Replace All.

    If you want to retain the correct space then you could use a formula in a helper column, eg:

    =LEFT(A1,2)&MID(A1,4,255)

    and copy this down, then fix the values.

    Hope this helps.

    Pete

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Removing Spaces from text

    Another way

    =SUBSTITUTE(B3," ","",1)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ 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