Strip first 5 characters from a cell.

[Mahnian]

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]

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.

 

[Mike H]

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

 

[Ed Ferrero]

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

 

[Mahnian]

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]

Glad I could help

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

 

[Don Guillett]

One way using the macro recorder to use data>text 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))

 

[ShaneDevenshire]

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.