Results 1 to 6 of 6

If formula for 'Begins with' text

This is a discussion on If formula for 'Begins with' text within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet with over 50k rows of data that I need to assign to 6 different individuals based ...

  1. #1
    New Member
    Join Date
    Nov 2010
    Posts
    2

    Default If formula for 'Begins with' text

    I have a spreadsheet with over 50k rows of data that I need to assign to 6 different individuals based alphabetically on customer name. Currently I text-to-columns on the customer name to separate the first letter (assume to column B). My formula I use at that point is:
    =IF(B2="A","Name1",IF(B2="B","Name2",IF(B2="C","Name3",))) etc, etc. til the entire alphabet is used.
    I need to find an easier way! It does work just fine for what I need (especially since I copy the formula from another spreadsheet & just paste in) but other people are scared to use it so my boss asked me to come up with something else.
    Is there a way to mark the If formula for 'begins with' text so I won't have to separate the name first? I am also willing to try a VBA macro, but I don't know how to do those (yet). I hate to ask someone to write the entire thing (unless you just happen to have exactly what I need already ). I need help to shorten this!

  2. #2
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: If formula for 'Begins with' text

    Try

    ="Name"&CODE(B2)-64

  3. #3
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: If formula for 'Begins with' text

    You can also use

    =IF(LEFT(A2,1)="A", ...

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    12,009

    Default Re: If formula for 'Begins with' text

    hurricanedaphne,

    Welcome to the MrExcel forum.

    What version of Excel are you using?

    Can we see some of your raw data?

    You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

    Here are three possible ways to post small (copyable) screen shots directly in your post:

    Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
    http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

    or
    http://RichardSchollarís beta HTML M...om of his post

    or
    Borders-Copy-Paste
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007, on a PC.

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,971

    Default Re: If formula for 'Begins with' text

    Try using a VLOOKUP table, e.g. list all the letters in Y2:Y27 and corresponding names in Z2:Z27 then use this formula in B2 copied down

    =VLOOKUP(LEFT(A2,1),Y$2:Z$27,2,0)

  6. #6
    New Member
    Join Date
    Nov 2010
    Posts
    2

    Default Re: If formula for 'Begins with' text

    Thank you so much for your help! And, per comment # 3, next time i will include screen shots !
    Thank you!
    Thank you!
    Thank you!!!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com