I need to remove the first 5 characters from every cell in column L and
replace the new string back into the originating cell.

Example:
337;#Error code
127;#Different Error Code

Would become--

Error Code
Different Error Code

So on and so forth.

Thank you in advance..
--Mahnian

Assuming the contents of the cell is less than 100 characters in length...

=MID(A1,6,99)

If there can be more than 99 characters, then use a number large enough to
accomodate your longest text.

--
Rick (MVP - Excel)


"Mahnian" wrote in message
...
I need to remove the first 5 characters from every cell in column L and
replace the new string back into the originating cell.

Example:
337;#Error code
127;#Different Error Code

Would become--

Error Code
Different Error Code

So on and so forth.

Thank you in advance..
--Mahnian

Hi,

You posted in programming so how about:-

Sub sonic()
Dim MyRange As Range
Set MyRange = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each C In MyRange
C.Value = Mid(C.Value, InStr(C.Value, "#") + 1)
Next
End Sub

Mike

"Mahnian" wrote:

I need to remove the first 5 characters from every cell in column L and
replace the new string back into the originating cell.

Example:
337;#Error code
127;#Different Error Code

Would become--

Error Code
Different Error Code

So on and so forth.

Thank you in advance..
--Mahnian

Rick Rothstein wrote'

Assuming the contents of the cell is less than 100 characters in length...

=MID(A1,6,99)

If there can be more than 99 characters, then use a number large enough to
accomodate your longest text.


Or for any length string =RIGHT(A1,LEN(A1)-5)

Ed Ferrero
www.edferrero.com

Sub Sonic was flawless, just what I needed. Even fond of the sub name, so
much so I named the button that calls it Sub Sonic..

Thanks

--Mahnian

"Mike H" wrote:

Hi,

You posted in programming so how about:-

Sub sonic()
Dim MyRange As Range
Set MyRange = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each C In MyRange
C.Value = Mid(C.Value, InStr(C.Value, "#") + 1)
Next
End Sub

Mike

"Mahnian" wrote:

I need to remove the first 5 characters from every cell in column L and
replace the new string back into the originating cell.

Example:
337;#Error code
127;#Different Error Code

Would become--

Error Code
Different Error Code

So on and so forth.

Thank you in advance..
--Mahnian

Glad I could help

"Mahnian" wrote:

Sub Sonic was flawless, just what I needed. Even fond of the sub name, so
much so I named the button that calls it Sub Sonic..

Thanks

--Mahnian

"Mike H" wrote:

Hi,

You posted in programming so how about:-

Sub sonic()
Dim MyRange As Range
Set MyRange = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each C In MyRange
C.Value = Mid(C.Value, InStr(C.Value, "#") + 1)
Next
End Sub

Mike

"Mahnian" wrote:

I need to remove the first 5 characters from every cell in column L and
replace the new string back into the originating cell.

Example:
337;#Error code
127;#Different Error Code

Would become--

Error Code
Different Error Code

So on and so forth.

Thank you in advance..
--Mahnian

One way using the macro recorder to use datatext to columns.
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 10/19/2008 by Donald B. Guillett
'

'
Range("B1:B2").Select
Selection.TextToColumns Destination:=Range("B1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1)), TrailingMinusNumbers:=True

End Sub
cleaned up
Range("B1:B2").TextToColumns Destination:=Range("B1"), _
DataType:=xlFixedWidth,FieldInfo:=Array(Array(0, 1), Array(5, 1))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mahnian" wrote in message
...
I need to remove the first 5 characters from every cell in column L and
replace the new string back into the originating cell.

Example:
337;#Error code
127;#Different Error Code

Would become--

Error Code
Different Error Code

So on and so forth.

Thank you in advance..
--Mahnian

Hi,

Here is Don's macro modified and very fast:

Sub Super()
Range([L1], [L65536].End(xlUp)).TextToColumns _
Destination:=[L1], DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(5, 1))
End Sub

And if you don't insert the line continuation character this is a one line
macro.

--
Thanks,
Shane Devenshire


"Don Guillett" wrote:

One way using the macro recorder to use datatext to columns.
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 10/19/2008 by Donald B. Guillett
'

'
Range("B1:B2").Select
Selection.TextToColumns Destination:=Range("B1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1)), TrailingMinusNumbers:=True

End Sub
cleaned up
Range("B1:B2").TextToColumns Destination:=Range("B1"), _
DataType:=xlFixedWidth,FieldInfo:=Array(Array(0, 1), Array(5, 1))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mahnian" wrote in message
...
I need to remove the first 5 characters from every cell in column L and
replace the new string back into the originating cell.

Example:
337;#Error code
127;#Different Error Code

Would become--

Error Code
Different Error Code

So on and so forth.

Thank you in advance..
--Mahnian