Find and Replace

[Michael]

Hi Folks - I have a range of cells that contain a single "character". The
character can be a digit or letter. I would like to prefix the contents of
each cell with the word "department,", so that a cell containing a 5 would
be replaced with department,5. Can this be done with Find and Replace?
Thanks.

Michael

 

[Steve]

Assume A1:A10 contains the department numbers you wish to prefix with
"Department, ".
1. In B1 (or similar cell in blank column) enter Department, followed by a
space.
2. In C1, enter =$B$1&A1.
3. Copy this down the column for each cell that contains a department
number.
4. Copy the entire range C1:C10 and paste values to A1.

HTH
Steve

 

[Michael]

Steve - I actually have about 35 columns that need to have department,
prefixed to the contents of the cell. I don't think concatenation works well
here. Is there a way using find and replace?

Michael

 

[Steve]

Michael -
I can't think of a way to use find and replace when the source cell only has
one character. Possibly a VBA solution would work.
Try posting again in excel programming. You should get a rapid response.
Steve

 

[Beege]

Hi Folks - I have a range of cells that contain a single "character". The
character can be a digit or letter. I would like to prefix the contents of
each cell with the word "department,", so that a cell containing a 5 would
be replaced with department,5. Can this be done with Find and Replace?
Thanks.

Michael

Michael,

Try this. Its almost search/replace?

Insert a row above your range.
In first pertinent cell, type ="Department "&C3 (of course you'd use the
reference below it)
Drag the cell across your columns (using the black handle in the lower right
corner)
Select the result
Copy
Select the first refernce (I used C3)
Paste/Special/Values.

I don't help often, so clarity isn't my strongest point...

Beege

 

[Steve]

Michael -

Try this VBA solution.

Select the range you want changed then run the macro. It will prefix the
contents of the cell with "Department," .


Sub AddDept()
For Each cell In Selection
If cell.Value <> "" Then cell.Value = "Department," & cell.Value
Next
End Sub

Let me know if you have any questions.

Steve

 

[Dave Peterson]

maybe add a check for length:

Sub AddDept()
dim cell as range
For Each cell In Selection
if len(cell.value) = 1 then
If cell.Value <> "" Then cell.Value = "Department," & cell.Value
end if
Next cell
End Sub

 

[Michael]

Awesome .... Thanks everyone!!

Michael

maybe add a check for length:

Sub AddDept()
dim cell as range
For Each cell In Selection
if len(cell.value) = 1 then
If cell.Value <> "" Then cell.Value = "Department," & cell.Value
end if
Next cell
End Sub