How do I replace a character with another?

In my spreadsheet I have the cells in column F referencing from cells in
column C. The original content in the cells in column C have spaces in them.
What I want to happen is have the cells in column E have all spaces ( )
replaced by periods (.). I know I can go to Edit>Replace, but the cells are
often updated and would be much easier if I didn't have to replace them every
time.

In cell C3:
A Stupid Example

In cell F3:
A.Stupid.Example

Thanks!

=SUBSTITUTE(C3," ",".")

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"MosesX8" <[email protected]> wrote in message
news:[email protected]...
> In my spreadsheet I have the cells in column F referencing from cells in
> column C. The original content in the cells in column C have spaces in
> them.
> What I want to happen is have the cells in column E have all spaces ( )
> replaced by periods (.). I know I can go to Edit>Replace, but the cells
> are
> often updated and would be much easier if I didn't have to replace them
> every
> time.
>
> In cell C3:
> A Stupid Example
>
> In cell F3:
> A.Stupid.Example
>
> Thanks!

There may be cleaner code than this..............
Used the macro recorder.

Range("A1:G12").Select
Selection.Replace What:=" ", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Add this into a vba module and give it a shrtcut or add it to the workbook
code
so that it can run each time before save or after whatever you
prefer..........
To get to the workbook code:
right click onto the excel picture on the left of the word "File" on the
excel menu.
select "view code"
select "workbook" in the dropdown list which shows (General)
select "before save" and paste the code between the sub()and end sub()
lines.

((Change the A1:G12 to suit.......))

Regards
--
Greetings from New Zealand
Bill K

"MosesX8" <[email protected]> wrote in message
news:[email protected]...
> In my spreadsheet I have the cells in column F referencing from cells in
> column C. The original content in the cells in column C have spaces in
> them.
> What I want to happen is have the cells in column E have all spaces ( )
> replaced by periods (.). I know I can go to Edit>Replace, but the cells
> are
> often updated and would be much easier if I didn't have to replace them
> every
> time.
>
> In cell C3:
> A Stupid Example
>
> In cell F3:
> A.Stupid.Example
>
> Thanks!

Thanks! Worked perfect. What if I wanted to substitute multiple characters?
In this case I want a space ( ), a colon (, forward (/) and back slash (\),
and a few more. It's basically any character that won't go into a filename,
with an exception for the space. Thanks again!

And Bill, I tried out that code, and it works great, but the simple
substitute worked fine in my case.

"George Nicholson" wrote:

> =SUBSTITUTE(C3," ",".")
>
> HTH,
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
>
> "MosesX8" <[email protected]> wrote in message
> news:[email protected]...
> > In my spreadsheet I have the cells in column F referencing from cells in
> > column C. The original content in the cells in column C have spaces in
> > them.
> > What I want to happen is have the cells in column E have all spaces ( )
> > replaced by periods (.). I know I can go to Edit>Replace, but the cells
> > are
> > often updated and would be much easier if I didn't have to replace them
> > every
> > time.
> >
> > In cell C3:
> > A Stupid Example
> >
> > In cell F3:
> > A.Stupid.Example
> >
> > Thanks!
>
>
>

Thanks for the feed back.
and George's solution is great.
For more than one character you may have to use the macro again.
Just copy the code several times and change the item you want to replace.
etc

--
Greetings from New Zealand
Bill K

"MosesX8" <[email protected]> wrote in message
news:[email protected]...
> Thanks! Worked perfect. What if I wanted to substitute multiple
> characters?
> In this case I want a space ( ), a colon (, forward (/) and back slash
> (\),
> and a few more. It's basically any character that won't go into a
> filename,
> with an exception for the space. Thanks again!
>
> And Bill, I tried out that code, and it works great, but the simple
> substitute worked fine in my case.
>
> "George Nicholson" wrote:
>
>> =SUBSTITUTE(C3," ",".")
>>
>> HTH,
>> --
>> George Nicholson
>>
>> Remove 'Junk' from return address.
>>
>>
>> "MosesX8" <[email protected]> wrote in message
>> news:[email protected]...
>> > In my spreadsheet I have the cells in column F referencing from cells
>> > in
>> > column C. The original content in the cells in column C have spaces in
>> > them.
>> > What I want to happen is have the cells in column E have all spaces ( )
>> > replaced by periods (.). I know I can go to Edit>Replace, but the cells
>> > are
>> > often updated and would be much easier if I didn't have to replace them
>> > every
>> > time.
>> >
>> > In cell C3:
>> > A Stupid Example
>> >
>> > In cell F3:
>> > A.Stupid.Example
>> >
>> > Thanks!
>>
>>
>>

Alright, well...before when I tried out that code I was doing it in regular
cells with no formulas. This time I was putting it in the right way on the
cells with formulas, and it doesn't work. Any way to work around this?
--
-----
MosesX8
I've got questions, you've got answers!


"Bill Kuunders" wrote:

> Thanks for the feed back.
> and George's solution is great.
> For more than one character you may have to use the macro again.
> Just copy the code several times and change the item you want to replace.
> etc
>
> --
> Greetings from New Zealand
> Bill K
>
> "MosesX8" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks! Worked perfect. What if I wanted to substitute multiple
> > characters?
> > In this case I want a space ( ), a colon (, forward (/) and back slash
> > (\),
> > and a few more. It's basically any character that won't go into a
> > filename,
> > with an exception for the space. Thanks again!
> >
> > And Bill, I tried out that code, and it works great, but the simple
> > substitute worked fine in my case.
> >
> > "George Nicholson" wrote:
> >
> >> =SUBSTITUTE(C3," ",".")
> >>
> >> HTH,
> >> --
> >> George Nicholson
> >>
> >> Remove 'Junk' from return address.
> >>
> >>
> >> "MosesX8" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > In my spreadsheet I have the cells in column F referencing from cells
> >> > in
> >> > column C. The original content in the cells in column C have spaces in
> >> > them.
> >> > What I want to happen is have the cells in column E have all spaces ( )
> >> > replaced by periods (.). I know I can go to Edit>Replace, but the cells
> >> > are
> >> > often updated and would be much easier if I didn't have to replace them
> >> > every
> >> > time.
> >> >
> >> > In cell C3:
> >> > A Stupid Example
> >> >
> >> > In cell F3:
> >> > A.Stupid.Example
> >> >
> >> > Thanks!
> >>
> >>
> >>
>
>
>

Moses
Not sure what you mean by doing it in cells with formula's.
The code does not make any distinction between normal or cells with
formula's.
Both will be changed.
The code needs to be entered in the vba editor into a module or into "this
workbook"

Any problems you can mail me a small example sheet and I can check the code.
It could be that there are added characters at the end of lines etc.

Greetings from New Zealand
Bill K
"MosesX8" <[email protected]> wrote in message
news:[email protected]...
> Alright, well...before when I tried out that code I was doing it in
> regular
> cells with no formulas. This time I was putting it in the right way on the
> cells with formulas, and it doesn't work. Any way to work around this?
> --
> -----
> MosesX8
> I've got questions, you've got answers!
>
>
> "Bill Kuunders" wrote:
>
>> Thanks for the feed back.
>> and George's solution is great.
>> For more than one character you may have to use the macro again.
>> Just copy the code several times and change the item you want to replace.
>> etc
>>
>> --
>> Greetings from New Zealand
>> Bill K
>>
>> "MosesX8" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks! Worked perfect. What if I wanted to substitute multiple
>> > characters?
>> > In this case I want a space ( ), a colon (, forward (/) and back
>> > slash
>> > (\),
>> > and a few more. It's basically any character that won't go into a
>> > filename,
>> > with an exception for the space. Thanks again!
>> >
>> > And Bill, I tried out that code, and it works great, but the simple
>> > substitute worked fine in my case.
>> >
>> > "George Nicholson" wrote:
>> >
>> >> =SUBSTITUTE(C3," ",".")
>> >>
>> >> HTH,
>> >> --
>> >> George Nicholson
>> >>
>> >> Remove 'Junk' from return address.
>> >>
>> >>
>> >> "MosesX8" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > In my spreadsheet I have the cells in column F referencing from
>> >> > cells
>> >> > in
>> >> > column C. The original content in the cells in column C have spaces
>> >> > in
>> >> > them.
>> >> > What I want to happen is have the cells in column E have all spaces
>> >> > ( )
>> >> > replaced by periods (.). I know I can go to Edit>Replace, but the
>> >> > cells
>> >> > are
>> >> > often updated and would be much easier if I didn't have to replace
>> >> > them
>> >> > every
>> >> > time.
>> >> >
>> >> > In cell C3:
>> >> > A Stupid Example
>> >> >
>> >> > In cell F3:
>> >> > A.Stupid.Example
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
>>
>>