+ Reply to Thread
Results 1 to 4 of 4

2 formulas in one cell Substitute and Trim

  1. 12-29-2011, 12:01 PM #1
    jimstrongy
    jimstrongy is offline
    Forum Contributor
    Join Date
    11-25-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    107

    2 formulas in one cell Substitute and Trim

    I have formula

    =substitute(B2,CHAR(160),"")
    And formula

    =TRIM(B2)

    How can I put them together in one cell??
    Reply With QuoteRegister To Reply
  2. 12-29-2011, 12:05 PM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: 2 formulas in one cell Substitute and Trim

    How about
    =Trim(substitute(B2,CHAR(160)," ")) ? (there needs to be a space between your quote marks)

    Here are all the text functions defined http://www.excelfunctions.net/Excel-Text-Functions.html
    you might also try "CLEAN()" to see if it does more for you.

    Note - Trim removes leading and trailing spaces and all double spaces in the string.
    Last edited by MarvinP; 12-29-2011 at 12:12 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 12-29-2011, 01:11 PM #3
    jimstrongy
    jimstrongy is offline
    Forum Contributor
    Join Date
    11-25-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: 2 formulas in one cell Substitute and Trim

    Thanks Marvin the formula works great.
    =clean actually doesn't do the job
    Reply With QuoteRegister To Reply
  4. 12-29-2011, 01:16 PM #4
    Andrew-R
    Andrew-R is offline
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: 2 formulas in one cell Substitute and Trim

    Quote Originally Posted by MarvinP View Post
    Note - Trim removes leading and trailing spaces and all double spaces in the string.
    Blimey, every day's a school day on here, isn't it? I've been using TRIM for years and years and never knew that. Thanks, Marvin.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1