I am trying to figure out how to append text at the end of cells in a column that has a number. Some cells do not have anything so I don't want to copy the word into those cells.
If I have column "B" that only has some cells with a number in them and I want to append the word "watts" behind the number, how would I do that?
I want to keep the number, but add the word "watts" at the end.
3 Answers
Assuming your data column (with some cells having numbers) is Column B as stated in the question, use the following formula for your cells in, say Column C:=IF(ISNUMBER(B1),B1&"Watts","")
This checks:
1. if the cell value is a number using the ISNUMBER() function
2. if yes, appends "Watts" using the string concatenation operator ("&")
3. else, it makes the cell blank (if you remove the "", you'd get the return value of the IF() function in the cell saying "FALSE")
Another way would be to use custom formatting to change the display in situ:
Select your whole range of data in column B, right click > Format Cells > Number > Custom
and in the "Type:" box type
0" watts"
exactly like that
That only changes the display so you can still do calculations with the numbers (like sum the column or similar).....and it won't be applied to cells that are blank or text.
Note: that format assumes integers as per the example shown, if you might want decimals or integers change to general" watts"
1Step 1: Select the range in which you will add specified text;
Step 2: Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
Step 3: Click Insert > Module, and paste the following VBA code in the Module Window.
Sub AppendToExistingOnRight()
Dim c as range
For each c in Selection
If c.value <> "" Then c.value = c.value & " watts"
Next
End SubStep 4: Press the F5 key to run this macro.
