How do you delete the first character (or 5th character) of all entries in a collumn in Excel?

Question

EG: if I have data formatted like:
IP192.168.0-dot-1
IP192.168.0-dot-2
IP192.168.0-dot-3

How do I use a formula in Microsoft Excel to automatically turn the values in those cells to:
192.168.0.1
192.168.0.2
192.168.0.3
etc.

Thanks!

Thanks for the answers thus far... very helpful in solving the problem I posed.

But how do you answer the question of how to MODIFY THE 1st or 5th character in a range of cells?

Suppose the data instead looked like this:
192.s168.0.1
192.%168.0.2
192.D168.0.3
192.>168.0.4

Or how about like this:

f192.168.0.1
sdkjhdfkghsdf192.168.0.2
36t5fg192.168.0.3
192.168.0.4

Thanks again for your consideration!

Or by extension:

sfgjsdlkjfg192.168.0.5e359ygehw
d2192.168.0.6tg
192.168.0.7!!
?192.168.0.8

PS:

If your answer includes VB, please include explain in concise yet clear English what the concepts behind your code is and instructions on how to implement the solution (assume I don't know how to use VB)

Answer 1

1. Launch the Find and Replace box usually F3
2. Click on the Replace... button
3 In the Find text box type -dot-
4. In the Replace text box type a .
5. Click Replace All
6. Do the same for IP and replace with nothing


To summarize you are replacing all occurances of -dot- with a . (period)

Answer 2

Actually, I would

1) Search and replace "IP" with nothing You will have:
192.168.0-dot-1
192.168.0-dot-2
192.168.0-dot-3

2) Search and replace "-dot" with "."

3) Then I would add a column to the right and use the TRIM to get rid of any unexpected spaces.

There are other ways, but this is the fastest.

Answer 3

for the additional part of your question, here is what i came up with
first I supposed that the data is in cell A1 to A8

press ALT+F11 to access the VB editor then in a new module paste the code below

Sub myIPmacro()
Dim myIPaddress As String
Dim i As Single

myIPaddress = vbNull

For Each cell In Range("A1:" & Range("A1").End(xlDown). Address )
For Each part In Split(cell, ".")
i = 0
Do
If IsNumeric(Right(part, Len(part) - i)) = True Then
Exit Do
End If
i = i + 1
Loop
Select Case myIPaddress
Case vbNull
myIPaddress = Right(part, Len(part) - i)
Case Else
myIPaddress = myIPaddress & "." & Right(part, Len(part) - i)
End Select
Next part
cell.Offset(0, 1) = myIPaddress
myIPaddress = vbNull
Next cell

End Sub

---------------------------------------------------------------------------
192.s168.0.1becomes 192.168.0.1
192.%168.0.2becomes 192.168.0.2
192.D168.0.3becomes 192.168.0.3
192.>168.0.4becomes 192.168.0.4
f192.168.0.1becomes 192.168.0.1
sdkjhdfkghsdf192.168.0.2becomes 192.168.0.2
36t5fg192.168.0.3becomes 192.168.0.3
192.168.0.4becomes 192.168.0.4

Answer 4

You don't need all these craps.
The Answer is simple and here...
Say your list starting from B2, then in C1, type 1 to remove charactor number 1, type 5 to remove charactor number 5, etc, then in C2, paste this
=Left( B2, $C$1-1)&MID( B2, $C$1 + 1, 500)

and fill it down to the rest of the list

Thats it, did you belive that the answer was that simple?

Enjoy my profile, I am the VBAXLMan

Answer 5

Edit>>Find and Replace

IP

Enter

To replace the dot with a .:

Edit>>Find and Replace
-dot-
.
Enter