Try
="Name"&CODE(B2)-64
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 ...
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!
Try
="Name"&CODE(B2)-64
You can also use
=IF(LEFT(A2,1)="A", ...
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.
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)
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