SOLVED

Add text prefix to numbers stored as text

%3CLINGO-SUB%20id%3D%22lingo-sub-1384914%22%20slang%3D%22en-US%22%3EAdd%20text%20prefix%20to%20numbers%20stored%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1384914%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20a%20new%20user.%20I%20need%20to%20modify%20a%20price%20list%20which%20has%20a%20column%20of%20product%20codes%2C%20numbers%20stored%20as%20text.%20I%20want%20to%20add%20a%20prefix%20to%20each%20number.%20i.e.%20original%20number%2012345%20to%20read%20as%20AB12345.%20I%20can%20see%20there%20are%20various%20functions%20which%20might%20do%20that%20but%20I%20can't%20see%20how%20to%20apply%20them.%20the%20prefix%20is%20stored%20as%20a%20name%20in%20a%20separate%20sheet%20of%20the%20spreadsheet.%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3EStephen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1384914%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1384948%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20text%20prefix%20to%20numbers%20stored%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1384948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665732%22%20target%3D%22_blank%22%3E%40Stephen_Buckland%3C%2FA%3E%26nbsp%3BYou%20can%20use%20the%20formula%26nbsp%3B%3DCONCATENATE%20if%20the%20data%20is%20in%20the%20same%20order%20as%20of%20price%20list.%20Otherwise%20use%20%3DVlookup%20to%20get%20respective%20text%20and%20use%20the%20above.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi,

I'm a new user. I need to modify a price list which has a column of product codes, numbers stored as text. I want to add a prefix to each number. i.e. original number 12345 to read as AB12345. I can see there are various functions which might do that but I can't see how to apply them. the prefix is stored as a name in a separate sheet of the spreadsheet.

Thanks in advance,

Stephen

1 Reply
Highlighted
Best Response confirmed by Stephen_Buckland (Occasional Visitor)
Solution

@Stephen_Buckland You can use the formula =CONCATENATE if the data is in the same order as of price list. Otherwise use =Vlookup to get respective text and use the above.