Returning text Values based on text

**Cameron1701** · 07-08-2013, 03:50 AM

Hi Guys

Wondering if you could help me out. In column B i have various values for an object named with a letter followed by a number e.g. "B25". I want to basically use the letters in this column to return a value in another column, for which the letter stands for, like B stands for "BUP". So far i got it with

Formula:

Please Login or Register  to view this content.

but column B is made up with other letters that i want included, like "S6" Which needs to be "SE" in the return column. Is there a way of expanding the formula i have already got?

**zbor** · 07-08-2013, 03:52 AM

You have wrong approach... Use VLOOKUP function

**Cameron1701** · 07-08-2013, 08:27 PM

still can't seem to get it, any ideas?

**FDibbins** · 07-08-2013, 08:40 PM

Hi and welcome to the forum

I think it will be easier if you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Cameron1701** · 07-08-2013, 09:12 PM

Ok no worries,

So in the "PIT" spreadsheet, I basically want values in column G to be automatically generated from the letter in my "pit no" in column B. For example where there is a "B" in column B i want the same row
in column G to equal "BUP", and so forth with "S" to be "SE", "HW" to be "HW" "M" to be "JP".

Any help is appreciated.

**FDibbins** · 07-08-2013, 10:18 PM

1st, create a table with the letter and the code it represents, something like this (I added a new sheet and put this in A1:B4)...
B.....BUP
S.....SE
HW...HW
M.....JP

Then use this ig G, copied down...
=VLOOKUP(LEFT(B2,IF(ISNUMBER(MID(B2,2,1)*1),1,2)),Sheet1!$A$1:$B$4,2,0)

**Cameron1701** · 07-08-2013, 11:21 PM

that helps so much! thanks

**FDibbins** · 07-08-2013, 11:30 PM

Happy to help

If you have extra codes tro add, add them to that table and then adjust the range in the formula accordingly

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.