+ Reply to Thread
Results 1 to 5 of 5

Remove space from beginning of text

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    833

    Remove space from beginning of text

    Hi guysa

    I have some text in column where there are spaces before the text

    I have tried using mid, trim functions

    It's not giving me the desired output and it is affecting lookups I want to do. If you see my sheet the text in cells a1 to a12 seem fine, it is a13 - a35 as an example that is where the text becomes a bit funny with spaces i want to remove

    thank you fro your help
    Attached Files Attached Files
    Thanks,

    R.



  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,531

    Re: Remove space from beginning of text

    Removed by author
    Last edited by Pepe Le Mokko; 08-25-2022 at 03:05 AM.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 23H2 64-bit)
    Posts
    88,186

    Re: Remove space from beginning of text

    Rayted is in the UK, so the formula will need commas:

    =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

    I would also change the final argument:

    =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),"")))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    833

    Re: Remove space from beginning of text

    Haha - Dirty data sounds right!

    Thank you both and thasnk you Ali - the 2nd of your formulas worked a charm!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 23H2 64-bit)
    Posts
    88,186

    Re: Remove space from beginning of text

    Happy to help.

+ 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. [SOLVED] Need to remove space before text in every cell
    By SM3T in forum Excel General
    Replies: 2
    Last Post: 06-19-2015, 01:38 AM
  2. [SOLVED] Remove Blanc Space, parse text, convert text to measurement
    By plans in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-25-2015, 07:50 PM
  3. Remove Space from first character of text
    By jaymkent in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2012, 07:19 AM
  4. Remove text between first and last space.
    By -James_ in forum Excel General
    Replies: 3
    Last Post: 10-24-2011, 12:02 PM
  5. Excel 2007 : Macro to remove text from beginning of line
    By galetondesigns in forum Excel General
    Replies: 3
    Last Post: 11-25-2008, 06:13 PM
  6. Remove Space in Text
    By Tian in forum Excel General
    Replies: 5
    Last Post: 04-06-2006, 02:15 PM
  7. How do I remove a blanck space from the beginning of a formula?
    By kan2953 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-06-2005, 01:38 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