rajncajn Posted December 3, 2009 Share Posted December 3, 2009 I have a large row of various number & I want to add a prefix to all the numbers. I could do the concatenate function, but that's a lot of hassle adding in new columns for the data, merging them & then deleting the old columns. Usually I do a Find & Replace if there is a specific character I can latch onto, but the data in the cells is all different in this case. Can I still do a Find & Replace and somehow tell it to replace the same data in the cell, but with the added prefix? Ex. Cell has FS004 in it, I need it to read LUT/FS004. Cell below it reads GA001, I need it to read LUT/GA001. Quote Link to comment Share on other sites More sharing options...
Controller Posted December 3, 2009 Share Posted December 3, 2009 (edited) Could you just make a formula like " ="LUT/"&c23 " where c23 equals the cell location, and then copy that formula next to all of the cells where the correct prefix is LUT/, then repeat for the other prefixes? Edited December 3, 2009 by Controller Quote Link to comment Share on other sites More sharing options...
rajncajn Posted December 3, 2009 Author Share Posted December 3, 2009 Could you just make a formula like " ="LUT/"&c23 " where c23 equals the cell location, and then copy that formula next to all of the cells where the correct prefix is LUT/, then repeat for the other prefixes? That does work, but I was trying to get away from having to add a column for the formula, then having to copy & paste the resulting value & then deleting the added column. I was hoping there was a quicker way of doing it. Quote Link to comment Share on other sites More sharing options...
Controller Posted December 3, 2009 Share Posted December 3, 2009 How many different prefixes are there? And how do you identify which cells get which prefix? Quote Link to comment Share on other sites More sharing options...
posty Posted December 3, 2009 Share Posted December 3, 2009 Sub AddPrefixLUT()Dim Cell As RangeOn Error Resume NextFor Each Cell In Selection.SpecialCells(xlConstants, 23) Cell.Value = "LUT/" & Cell.ValueNext CellEnd Sub Quote Link to comment Share on other sites More sharing options...
rajncajn Posted December 3, 2009 Author Share Posted December 3, 2009 How many different prefixes are there? And how do you identify which cells get which prefix? several per spreadsheet, but they are grouped & all the cells are in the same column. So all I have to do is highlight the cells I am working with. What the data is is a series of valve, gauge & fitting numbers that are listed in the spread sheet with ship board discrepancies found related to that fitting. Only some of the people who are notating the discrepancies on the ships fail to provide all of the information such as location and system identifier. So what I would be looking at is one column of fitting numbers next to a column listing the discrepancy (valve not installed, valve damaged etc). The valve number may be V0026, but with out the prefix LUF there is no way to distinguish what piping system that valve number refers to and when you have to cross reference the valve number with with the data sheet that tells you where the valve is located on the ship you end up with maybe 20 valves all with the number V0026. One may be in the fuel system, one in potable water, one in firemain etc. So in order to make it easier to look up the location data for whoever will be correcting the discrepancy or verifying that the work has been done I have to add the system prefix Here's a small example. This group of numbers should all have the prefix "LUF/" to identify that they are in the Reduction Lube Oil System. SYS # | DiscrepancyV0163 | NO LABEL PLATE INSTALLED FOR MRG LUBO SVCE STRAINER NO 1 SAMPLING CUTOUTV0081 | NO LABEL PLATE INSTALLED FOR MRG LUBO CLR 2 SPLY CUTOUT VALVE.V0077 | MRG LUBO CLR 2 OUTLET CUTOUT VALVE INCORRECTLY LABELED "2-RLO-V-34." VALVE SHOULD BE LABELED "2-RLO-V-35."V0162 | NO LABEL PLATE INSTALLED FOR MRG LUBO SVCE STRAINER NO 2 SAMPLING CUTOUTV0096 | MRG STBY/EMER LUBO PMP 2A INLET VALVE COULD NOT BE LOCATED. VERIFY VALVE IS INSTALLED AND CORRECTLY LABELED.V0222 | MRG SPLY LINE LOW POINT DRAIN CUTOUT VALVE COULD NOT BE LOCATED. VERIFY VAVLE IS INSTALLED AND CORRECTLY LABELED.V9003 | UNL VALVE SENSING LINE VENT CUTOUT VALVE IS INCORRECTLY LABELED "1-RLO-V-9003." LABEL PLATE SHOULD READ "2-RLO-V-9003."V9002 | UNL VALVE SENSING LINE ROOT VALVE IS INCORRECTLY LABELED "1-RLO-V-9002." LABEL PLATE SHOULD READ "2-RLO-V-9002."GA037 | NO LABEL PLATE INSTALLED FOR MN THR BRG SPLY LINE PRESS GAGEV0183 | NO LABEL PLATE INSTALLED FOR MN THR BRG VENT LINE VALVE.PS015 | NO LABEL PLATE INSTALLED FOR MN THR BRG SPLY LINE PRESS SWITCH.TH013 | NO LABEL PLATE INSTALLED FOR MN THR BRG THRUST CAVITY OIL OUTLET THERMOMETER.TH014 | NO LABEL PLATE INSTALLED FOR MN THR BRG THRUST JOURNAL CAVITY OIL OUTLET THERMOMETER.V0164 | NO LABEL PLATE INSTALLED FOR MN THR BRG THRUST JOURNAL CAVITY DRAIN CUTOUT VALVE.V0153 | NO LABEL PLATE INSTALLED FOR MN THR BRG SPLY LINE PRESS GAGE CUTOUT VALVE.V0167 | NO LABEL PLATE INSTALLED FOR MN THR BRG THRUST JOURNAL CAVITY DRAIN CUTOUT VALVE.V0253 | MRG LUBO PMP PRELUBE CUTOUT VALVE FROM PURIFIER. VALVE HAS DUPLICATED NAVY CID NUMBER "2-RLO-V-45." LABEL PLATE SHOULD READ "2-RLO-V-44."F1010 | SIGHT FLOW INDICATOR LABELS ARE SWAPPED. 1-RLO-F-61 SHOULD SERVICE MRG STBY/EMER LO SVCE PMP 1A. 1-RLO-F-62 SHOULD SERVICE MRG STBY/EMER LO SVCE PMP 1B. . Quote Link to comment Share on other sites More sharing options...
CaP'N GRuNGe Posted December 3, 2009 Share Posted December 3, 2009 http://www.mrexcel.com/forum/index.php Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.