Adding a name\word to a range of cells without deleting the data already in each cell.

[sbarwald]

Hi I am trying to find a way for adding a name\word to a range of cells without deleting the data already in each cell.

I can sort of get it working by..


Adding two spaces to the end of the data in each cell.
Selecting the cells I want to add a word to.
Use find and replace to find two spaces then replace with the word\name with two spaces after the name.
This works, but if a cell in the range has no data, excel wont leave a cell blank with two spaces. The spaces disappear. So find and replace does not work for blank cells.

Wondering if there is another way to add a word to the end of the data in a bunch of selected cells?

[ByteMarks]

Does this work for you?

Please Login or Register  to view this content.

[sbarwald]

Hi ByteMarks, thanks so much for you post , unfortunately we use the file on excel for web to schedule around 15 machines & 40 people. As you would know, VBA macros don't work on excel online.

I am a very average excel user. I have tried to automate the attached file to...

• Show available people (row 98, names clear as names are added above)
• Count number of names (row 2)
• Look for duplicates (row 1)
• Count number of machines working (row 3 )
• Alphabetize people on leave (row 93)
• Add people to leave on row 92
• There are also a number of conditional formatting rules to highlight mistakes and current date

I tried your macro and it works perfectly in a .xlsm, such a bummer macros don't work on excel for web.
If you have any other suggestions or ways I could add names on mass to row 92 or any other ways to improve this file it would be really appreciated.
FYI For privacy I have removed our clients company's names replaced with ZZ
All the formulas work pretty well it only glitches if I drag names vertically.

[ByteMarks]

Maybe something like this in Office Script

function main(workbook: ExcelScript.Workbook, TextToAdd: string) { 
  let sheet = workbook.getActiveWorksheet(); 
  let SelRng= workbook.getSelectedRange(); 
  let NumRows = SelRng.getRowCount(); 
  let NumCols = SelRng.getColumnCount(); 
   
    for (let r=0; r < NumRows;r++){ 
      for (let c=0; c< NumCols; c++) { 
        let v = SelRng.getCell(r, c).getValue() as string; 
        v.trim() 
        if (v.length == 0) { 
          //blank cell 
          SelRng.getCell(r,c).setValue(TextToAdd); 
        } else { 
        //populated cell 
          if (v.includes(TextToAdd) == false) { //don't add if cell already contains it. May or may not be required. 
            SelRng.getCell(r,c).setValue(v + " " + TextToAdd) 
          }  
        }     
      } 
    } 
  }