Removing Text

[infinite1013]

I have a column of text that I need to remove one letter from. The cells are
formatted like this- AT&T DF ZS017 46336. I need to remove the Z. I would go
through and do them individually, but there are about 16,000 of them. Is
there a formula that will do this?

 

[Mike H]

Try this and drag down

=SUBSTITUTE(A1,"Z","")

Mike

 

[Rick Rothstein \(MVP - VB\)]

More details are needed...

Is it **always** a Z that is being removed or was that an example and you
really want the first letter (no matter what it is) of the second from the
end grouping removed?

Is the last group always 5 characters long?

Is the second group from the end always 5 characters long?

Is there always a single space between the last two groups?

Rick

 

[infinite1013]

It is the first letter of that group. All of my info is in that exact format,
so, yes, both groups contain the five characters. There is always a single
space there. Thanks.

 

[Rick Rothstein \(MVP - VB\)]

This formula should do what you want...

=REPLACE(A1,LEN(A1)-10,1,"")

Rick

 

[Rick Rothstein \(MVP - VB\)]

It just occurred to me that you might be wanting to modify your actual data
rather than repeat it, with the letter removed, in another column. If that
is the case, you can use this macro for that...

' Change the two Const assignments to the column
' for your data and to row your data starts on
'
Sub RemoveOneLetter()
Dim X As Long
Dim LastRow As Long
Dim C As String
Const DataColumn = "A"
Const DataStartRow = 2
With Worksheets("Sheet3")
LastRow = .Cells(Rows.Count, DataColumn).End(xlUp).Row
For X = DataStartRow To LastRow
C = .Cells(X, DataColumn).Value
If C Like "* [A-Za-z][A-Za-z]### #####" Then
Mid(C, Len(C) - 11) = Chr(1) & Chr(1)
.Cells(X, DataColumn).Value = Replace(C, Chr(1) & Chr(1), " ")
End If
Next
End With
End Sub

Since you said that your text was always of the same form, namely, its last
11 characters are always

<space><letter><letter><digit><digit><digit><space><digit><digit><digit><digit><digit>)

I built in a little extra protection for you. Since changes made by a macro
cannot by undone, and since the "shape" of the last 11 characters was fixed,
I decided it was easy enough to protect you from accidentally running the
macro against data that has already been modified. If you need to add more
data to the end of your list and then remove the indicated letter from this
new data, you can just re-run the macro and it will only "touch" your new
data.

Rick