+ Reply to Thread
Results 1 to 5 of 5

Auto filling cell based on initial letters in previous cell

  1. #1
    Registered User
    Join Date
    11-29-2019
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    1

    Question Auto filling cell based on initial letters in previous cell

    So I need help trying to figure this one out.

    I can't give too much details as the data is sensative but I have a column with policy numbers for different products they start with 3 letters and end with 9 numbers, the initial 3 letters (for instance "HPT, YTM" etc) are indicators as to the policy type.

    So I have the policy number column and then the policy type column, is there a way to auto populate the "policy type" column based on the initial 3 letters in the corresponding "policy number" cell?

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,865

    Re: Auto filling cell based on initial letters in previous cell

    You could set up a table of two columns somewhere (e.g. in columns X and Y), where you list in column X all the 3-letter codes and in column Y the policy types associated with each code, then you could use a formula like this:

    =IFERROR(VLOOKUP(LEFT(A2,$X:$Y,2,0),"not found")

    assuming that your policy number is in A2. Then you can copy the formula down as far as you need to.

    Hope this helps.

    Pete

  3. #3
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    447

    Re: Auto filling cell based on initial letters in previous cell

    If you need to populate policy type ie the three first digits based on the policy number, can you use the LEFT function which returns the three characters from the left
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you think someone helped you, click on the "* Add Reputation" as a way to say thank you.

    If your problem is solved, go to Thread Tools and select Mark This Thread Solved

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,865

    Re: Auto filling cell based on initial letters in previous cell

    Oops, I've just noticed a mistake in the formula I posted - it should be this:

    =IFERROR(VLOOKUP(LEFT(A2,3),$X:$Y,2,0),"not found")

    Hope this helps.

    Pete

  5. #5
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Auto filling cell based on initial letters in previous cell

    Hi Hermanator,

    Try this.

    =IF(ISNUMBER(SEARCH("HPT",$A2)),"POLICY TYPE 1",IF(ISNUMBER(SEARCH("YTM",$A2)),"POLICY TYP2",IF(ISNUMBER(SEARCH("XYZ",$A2)),"POLICY TYPE 3","NOT FOUND")))

    Adjust Policy Type to suit and copy down.

    Regards

    peterrc
    Last edited by peterrc; 12-01-2019 at 06:53 AM. Reason: Addressed to wrong person!

+ 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. Help auto filling using column letters instead of numbers
    By T.ajt in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-31-2019, 11:39 PM
  2. Replies: 5
    Last Post: 09-28-2016, 07:02 PM
  3. [SOLVED] Auto filling a cell with data from a different sheet based on Today()
    By idaho hillbilly in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-11-2014, 06:30 PM
  4. Macro - how to auto populate the data, based on the previous cell values
    By Avinash Kumar in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-21-2012, 02:03 PM
  5. Replies: 2
    Last Post: 02-02-2011, 12:11 AM
  6. Auto-update initial drop-down selection based on previous drop-downs
    By thornomad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2010, 09:55 AM
  7. Macro to auto populate the destination cell based on the values of the previous cell
    By Avinash Kumar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-05-2009, 02:06 AM

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