Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Text
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Text
Try this and drag down
=SUBSTITUTE(A1,"Z","") Mike "infinite1013" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Text
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Text
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)" wrote: 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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Text
This formula should do what you want...
=REPLACE(A1,LEN(A1)-10,1,"") Rick "infinite1013" wrote in message ... 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)" wrote: 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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Text
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 "infinite1013" wrote in message ... 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)" wrote: 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing text | Excel Discussion (Misc queries) | |||
Removing text from a cell with text and numbers | Excel Discussion (Misc queries) | |||
Removing text | Excel Discussion (Misc queries) | |||
removing blanks at the end of text | Excel Worksheet Functions | |||
Removing Text | Excel Worksheet Functions |