Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Brain Clog! Help with formula, please
I have a column of UPC numbers, most of which are 10 characters, which is
all we need for our purposes. But, some contain an 11th check digit at the beginning, which we do NOT want, since it interferes somewhat with reading accuracy. Examples: Some look like: 3600012345 Others look like: 73600012345 I want to remove the 7, and also split the remaining 10 digits with a dash, so we get this: 36000-12345 No calculation is ever done with these characters - they're used only as text. I know how to use the RIGHT() & LEFT() functions, so I can strip off the chars I want, but I do NOT know how to have Excel check to see if there are 11 digits and automatically get rid of the first one. I can do it in Access or Paradox, but I'd rather not add an extra chore, since I'll be receiving these files once a week. I suppose I could sort the whole list, which would put all the 11-char string in one place, and then use two different string formulae to handle the different groups, but that's not very elegant. Help! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking portions of a formula | Excel Worksheet Functions | |||
assign formula to another cell | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |