+ Reply to Thread
Results 1 to 4 of 4

formula to display the first four non-blank characters from anther cell

  1. #1
    Registered User
    Join Date
    05-02-2005
    Posts
    67

    formula to display the first four non-blank characters from anther cell

    I have a spreadsheet does sales quotes and I wanted to create a unique quote name that get concatenated with data from multiple cells (using "&" between the multiple fields. The first piece of data is the Company Name and I want to pick the first four, non-blank characters. I use Left(b2,4) and this works for most cases.

    Here are example of what I want:

    Philadelphia Phillies ---> now produces "PHIL", which is OK.
    Washington Capitals ---> now produces "WASH"
    Los Angeles Lakers ---> now produces "LOS " but I would like "LOSA"
    San Francisco 49ers ---> now produces "SAN " but I would like "SANF"
    El Paso Chihuahuas ---> now produces "EL P" but I would like "ELPA"

    How do I ignore the spaces after the second or third character and fill in the appropriate number of characters after the space?

    Thanks in advance for your help!!!
    Jim
    Last edited by jshrader; 05-18-2015 at 10:22 PM.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: formula to display the first four non-blank characters from anther cell

    Perhaps :

    =UPPER(LEFT(SUBSTITUTE(A1," ",""),4))

  3. #3
    Registered User
    Join Date
    05-02-2005
    Posts
    67

    Re: formula to display the first four non-blank characters from anther cell

    Thanks you for the speedy response! That is exactly what I needed.

    Thanks!!!

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: formula to display the first four non-blank characters from anther cell

    thanks for feedback too

+ 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] Row indexing formula to only display non-blank cell values
    By Platinum3x in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-30-2014, 06:00 PM
  2. [SOLVED] Sum formula ignore display cell blank
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-30-2014, 07:24 PM
  3. Replies: 2
    Last Post: 11-01-2011, 05:14 PM
  4. Replies: 4
    Last Post: 03-17-2006, 04:30 PM
  5. [SOLVED] Can a formula display output to an otherwise blank cell?
    By T.Morris-TX in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-29-2005, 08:35 AM

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