Results 1 to 4 of 4

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

Threaded View

  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.

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. 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