+ Reply to Thread
Results 1 to 3 of 3

Generating: letters from cell contents, & numbers based on number of cells containing data

  1. #1
    Registered User
    Join Date
    04-18-2013
    Location
    London
    MS-Off Ver
    Excel 2010 Starter
    Posts
    6

    Generating: letters from cell contents, & numbers based on number of cells containing data

    Hi,

    Looking for help with the following:

    ================================
    E.g. Column A | B | C

    Row 1) John Smith
    Row 2) Noah Ava Jones | Liam Mia Jones
    Row 3) Jacob Taylor | Sophia Taylor | William Taylor
    ================================

    I need formulas to create 4-digit references on each row.

    - The first 3 digits will be 3 letters from the last word (surname) contained in the first column. In the above example, I should have this result: 1) S M I _, 2) J O N _, 3) T A Y _.
    - The 4th digit will be a number indicating number of cells containing data within a range. I should have this result: 1) S M I 1, 2) J O N 1, 3) T A Y 3.

    Thanks

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Generating: letters from cell contents, & numbers based on number of cells containing

    Hi.

    =UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),3))&COUNTIF(A1:C1,"?*")

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Generating: letters from cell contents, & numbers based on number of cells containing

    in green the result.

    i used helpcells to get the result.

    it can be done in one formula, but that makes it not easy to understand the formula.

    see the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. separating numbers and letters from alphanumeric cell contents
    By Ron Rosenfeld in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 09-07-2005, 12:05 AM
  2. separating numbers and letters from alphanumeric cell contents
    By Harlan Grove in forum Excel Formulas & Functions
    Replies: 35
    Last Post: 09-06-2005, 07:05 PM
  3. [SOLVED] separating numbers and letters from alphanumeric cell contents
    By PH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  4. separating numbers and letters from alphanumeric cell contents
    By PH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  5. separating numbers and letters from alphanumeric cell contents
    By PH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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