Find & Replace Special characters with numbers
 

I \ve just spent the last hour reviewing the "Find & Replace" questions &
solutions, and trying methods from similar problems with no luck.

I have the following type of numbers (PLC Addresses) that I'm changing from
Bit address to Word Address:
17:00
17.0/1
17.0/2
17.0/3
17.0/4
17.0/5
17.0/6
17.0/7

They need to look like this:
17:0
17:1
17:2
17:3
17:4
17:5
17:6
17:7

I'm doing a "find & Replace", "Find" ".0/" & Replace with ":", and the cell
formatting has been tried with "General" & "Text"

For some reason it changes my numbers to "Date & Time"
formatting. I then tried "Find"~.0~/" & Replace with ":",

that got me closer, but I get this value: "17:00", which if you check the
formatting is now been changed to "Time" formattting.

So then I tried: "Find"~.0~/" & Replace with "~:", and got this value:
"17~:0",
so I tired "Find"~.0~/" & Replace with ":~", and got this value: "17:~0"

I'm so close but obviously missing something. I wasted most of my date
trying to search for an easy solution, and I have over 200 numbers like this
to edited.

So I'm hoping for a quick solution

--
Still Playing Soccer

Find & Replace Special characters with numbers
 

formula.
=LEFT(H2,3)&RIGHT(H2,1)
or macro
for each c in selection
c.value=LEFT(c,3)&RIGHT(c,1)
next c
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Soccer" wrote in message
...
I \ve just spent the last hour reviewing the "Find & Replace" questions &
solutions, and trying methods from similar problems with no luck.

I have the following type of numbers (PLC Addresses) that I'm changing
from
Bit address to Word Address:
17:00
17.0/1
17.0/2
17.0/3
17.0/4
17.0/5
17.0/6
17.0/7

They need to look like this:
17:0
17:1
17:2
17:3
17:4
17:5
17:6
17:7

I'm doing a "find & Replace", "Find" ".0/" & Replace with ":", and the
cell
formatting has been tried with "General" & "Text"

For some reason it changes my numbers to "Date & Time"
formatting. I then tried "Find"~.0~/" & Replace with ":",

that got me closer, but I get this value: "17:00", which if you check the
formatting is now been changed to "Time" formattting.

So then I tried: "Find"~.0~/" & Replace with "~:", and got this value:
"17~:0",
so I tired "Find"~.0~/" & Replace with ":~", and got this value: "17:~0"

I'm so close but obviously missing something. I wasted most of my date
trying to search for an easy solution, and I have over 200 numbers like
this
to edited.

So I'm hoping for a quick solution

--
Still Playing Soccer

Find & Replace Special characters with numbers
 

st, repace 17 with '17. this will mark as text. then do your next replace
of .0/ with :

"Soccer" wrote:

I \ve just spent the last hour reviewing the "Find & Replace" questions &
solutions, and trying methods from similar problems with no luck.

I have the following type of numbers (PLC Addresses) that I'm changing from
Bit address to Word Address:
17:00
17.0/1
17.0/2
17.0/3
17.0/4
17.0/5
17.0/6
17.0/7

They need to look like this:
17:0
17:1
17:2
17:3
17:4
17:5
17:6
17:7

I'm doing a "find & Replace", "Find" ".0/" & Replace with ":", and the cell
formatting has been tried with "General" & "Text"

For some reason it changes my numbers to "Date & Time"
formatting. I then tried "Find"~.0~/" & Replace with ":",

that got me closer, but I get this value: "17:00", which if you check the
formatting is now been changed to "Time" formattting.

So then I tried: "Find"~.0~/" & Replace with "~:", and got this value:
"17~:0",
so I tired "Find"~.0~/" & Replace with ":~", and got this value: "17:~0"

I'm so close but obviously missing something. I wasted most of my date
trying to search for an easy solution, and I have over 200 numbers like this
to edited.

So I'm hoping for a quick solution

--
Still Playing Soccer