Trying to find a way to find and add text in front of found text.

Copper Contributor

I'm trying to find a way in Excel to find text and then add a character in front of found text. For example Find: Yellow Filled cell with any number and add D in front of found number. Is there anyway to do this quickly?

7 Replies

@Stephan475 

 

Yes it's possible.

 

Easiest to do if the yellow color in the cell (background, I presume) is there as a result of Conditional Formatting. Because if it's there in Cell A1 as a result of Conditional Formatting, that means there's a formula that can be written in B1 using the same conditional (IF) clause that leads to the conditional format.

 

In effect you'd want to write a formula that says IF (specified condition exists in A1) THEN ("D"&A1) , the ampersand (&) being the short-cut way to say Concatenate these two things.

 

If what's in A1 is a number, formatted as a number, then you'd have to use the TEXT function to convert it to text before concatenating. That could all be done in the one formula.

 

Now, I'm assuming you were just asking to be pointed in the right direction.....if you need more complete example, actually in a spreadsheet, by all means come back.

@mathetes  Thank you, I'll try this out. I'm an Excel beginner so, if you have any obvious suggestions to perform this task that would be much obliged. Thanks again for your comment.

@mathetes Also this is what I'm currently working with. I'm trying to build a schedule that shows the number of days a person had off since the last time they pulled a Duty Shift. The days off are being annotated by a number that counts up everyday they have a break from Duty until they pull it again in which case the count resets. The "D" signifies they have another type of duty and are ineligible to work the Duty Shift but, the count still continues. The count is essential to the schedule because that's how I fairly determine who should work the Duty Shift.

@Stephan475 

 

If you're asking for help, you can help me by uploading the actual spreadsheet you used to create that image. An image by itself is truly not anywhere near as useful as the actual spreadsheet.

 

Also, I don't fully follow your description. I'm sure it's clear in your mind, but maybe I'm confused by "D" meaning other Duty, and you're counting Days off  since a Duty shift....

 

So if you could give a couple of examples of a sequence of days with DaysOff noted, Other Duty Days, Duty Shift Days.... the annotated count for the days off.... that would help. Along with the spreadsheet itself in whatever form it currently exists.

@Stephan475 

Filter the rows with Yellow filled cells and in next column =CONCATENATE("Cell"&"D") and copy this for all rows. There you have your Yellow filled entries having D in the end of number in each cell.

@mathetes The schedule which is known as a DA6 in my organization is formatted in the following way: I record the number of days each Soldier has had off since they performed Staff Duty, (recorded as a yellow filled in "X"), in numerical order.

 

Soldiers who are unavailable for Staff Duty will be given the following abbreviations:  A, (for leave, pass, special duty, temporary duty, sick-in quarters, any other authorized reason) D, (for duty that prevents the Soldier from assuming duty that day) U, (for Soldier’s absent without leave, in arrest, in confinement, or otherwise unavailable due to their own misconduct). 

 

Whenever the abbreviation “A” is used, the numbering sequence will be interrupted.  Whenever the abbreviation “D” or “U” is used the numbering sequence will continue with the appropriate abbreviation included.

 

I've attached the most recent schedule I used. The schedule I should mention is governed by my organization's regulations and policies so the abbreviations can't be altered neither can the method of counting. 

Also, sorry for my late reply I ended up just doing the work manually because, of the due date was imminent. 

@Stephan475 

 

Wow. This is NOT an easy one, not by any means. I'm at a loss at coming up with a formula that can do it, or some combination of formulas and data validation....

 

I invite some of the more advanced experts here (those with "MVP" next to their profile names) to contribute.

 

Let me ask you a question or two, though, that might take us in the direction of a solution.

  • You've written, and I understand, "The schedule I should mention is governed by my organization's regulations and policies so the abbreviations can't be altered neither can the method of counting. "
  • What I wonder, though, is whether this actual output format is itself not subject to change? That is, do you really need to see a full roster with all names, every day of the month accounted for with one code or another, ALL on one page? I do realize you can see a pattern with your current array, but I'm not sure the helpful (supporting leadership decision making) information per se is as clear; I can imagine the commanding officer looking at this array and wondering, "What is this telling me?"
  • Might it be just as useful, let's say, to show simply the current status of each individual, i..e., that final column worth of data, perhaps sorted in descending order by days since last Staff Duty ("X") day.
  • Perhaps a separate listing of those with "D" or "U" and the associated number of days they've been in that status.
  • Same with "A" individuals?

The reason I ask all that is this: I can envision (although I've not scoped it out in detail) creating an Excel Table in which each individual has a record that consists of name, rank, serial number (seriously, all that; serial number is unique whereas name and rank are not) and last date of Staff Duty, as well as relevant recording of whether "D", "U", "A" applies and date that started. Maybe one or two other pieces of info.

 

Based on that kind of record at the "input" end of things, it would be possible to create dynamically, as needed, the kind of  reports that I hint at above. Your maintenance task would be simply to note when somebody enters "D" or "U" status (or performs Staff Duty), etc.  You'd then print the output reports when needed. The default process, for most (it would appear) is that days just keep getting counted until an X restarts them.

 

Anyway, it's an altogether different way to approach this, but it would mean re-thinking and possibly changing not the rules, not the codes, not the method of counting, but yes, changing the way it all gets displayed. My own thought is that it would in fact be clearer.

 

Absent that, if you do need to keep showing it as you've been doing, I will defer to the MVPs.