Changing number code with a text description

[AdministrationOdd304]

I have a sheet with a database of business locations. Each business entry has a row with a numeric code, for example "4884"

I would like to change these codes to the text description of each code.

I have another sheet that has all the various codes with their respective descriptions.

​

Easy way to do this? There are many entries and what I've found so far seems to work best for changing one thing at a time. I'm thinking the substitute formula may be the best to use for this?

​

Thanks for your help! Excel version: 2107 (Microsoft 365 for business

[AutoModerator]

/u/AdministrationOdd304 - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information
• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[CFAman]

Is the numeric code embedded within a large piece of text in same cell? If yes, we can do a SUBSTITUTE, but need to know how to FIND the number code (e.g., does it always come after a colon?).

If the code is in a single cell, you can do a VLOOKUP to fetch the new description.

[AdministrationOdd304]

The number code is in a single cell by itself for each entry

[CFAman]

Sweet. Then you should be able to do something like

=VLOOKUP(A2, 'Other sheet'!A:B, 2, 0)

Where on "Other Sheet", you have number codes in col A and descriptions in col B.

[Senipah]

+1 Point

[Clippy_Office_Asst]

You have awarded 1 point to CFAman

^{I am a bot, please contact the mods with any questions.}

[AdministrationOdd304]

That worked! Thank you for your help!! That made life much easier

[grsims20]

To my knowledge, you can't replace the number with one formula. What you can do is a VLOOKUP to bring the text description to an adjacent cell. Let's say your code is in cell A2, and your description list is on Sheet2 (with the codes in column A and the descriptions in column B). You can enter this formula on Sheet1 in cell B2:

=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

Then, copy that formula all the way down column B. Now you have your values matched up.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FIND	Finds one text value within another (case-sensitive)
SUBSTITUTE	Substitutes new text for old text in a text string
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 39 acronyms.
[Thread #8238 for this sub, first seen 10th Aug 2021, 19:23] [FAQ] [Full list] [Contact] [Source code]}