Forum Discussion
Trying to find a way to find and add text in front of found text.
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?
8 Replies
You can easily accomplish this in Excel by using the "Find and Replace" feature combined with a bit of formula work. First, you could use the =IF(ISNUMBER(SEARCH("Yellow", A1)), "D"&A1, A1) formula to add "D" in front of the number if the cell contains the word "Yellow." Then, you can copy and paste the result over the original data if needed. Also, if you're interested in checking Kentucky public records, like criminal or court records, you can visit Ky criminal records for more information.
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.
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 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.
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.
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.
