Find and Replace

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael - 14 Sep 2006 18:13 GMT

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

Reply to this Message

Steve - 14 Sep 2006 18:32 GMT

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

> 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
[quoted text clipped - 3 lines]
>
> Michael

Reply to this Message

Michael - 14 Sep 2006 18:49 GMT

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

> Assume A1:A10 contains the department numbers you wish to prefix with
> "Department, ".
[quoted text clipped - 15 lines]
>>
>> Michael

Reply to this Message

Steve - 14 Sep 2006 19:07 GMT

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

> 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
[quoted text clipped - 21 lines]
>>>
>>> Michael

Reply to this Message

Steve - 14 Sep 2006 19:29 GMT

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

> Michael -
> I can't think of a way to use find and replace when the source cell only
[quoted text clipped - 27 lines]
>>>>
>>>> Michael

Reply to this Message

Dave Peterson - 14 Sep 2006 19:35 GMT

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 -
>
[quoted text clipped - 44 lines]
> >>>>
> >>>> Michael

Signature

Dave Peterson

Reply to this Message

Michael - 14 Sep 2006 20:53 GMT

Awesome .... Thanks everyone!!

Michael

> maybe add a check for length:
>
[quoted text clipped - 62 lines]
>> >>>>
>> >>>> Michael

Reply to this Message

Beege - 14 Sep 2006 19:25 GMT

> 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
[quoted text clipped - 3 lines]
>
> 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

Reply to this Message