Removing text from cells

Hi,

I have a spreadsheet with a list of reference numbers in 
column A. The numbers are like this.

AAA/12345/Smith J Mr

What I want to do is remove the text after the last / eg. 
Smith J Mr.

I have thousands of cells I need to remove this on.

Please help.

Thanks.

0
anonymous (74722)
7/9/2004 7:21:09 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
601 Views

Similar Articles

[PageSpeed] 7

Hi Craig

are all the entries the same number of characters for the first two
sections,
AAA/12345/
BBB/67890/
etc

if so you can do this using a "helper" column - in column B type
==LEFT(A1,10)
and then double click on the fill handle (bottom right hand side of cell) to
fill down
now copy column B
click on A1 and choose Edit / Paste Special Values
and then you can delete column B

(however, before trying this please take a backup of your workbook).

please let us know how you go

cheers
JulieD


"Craig" <anonymous@discussions.microsoft.com> wrote in message
news:297f401c46585$4e41b000$a501280a@phx.gbl...
>
> Hi,
>
> I have a spreadsheet with a list of reference numbers in
> column A. The numbers are like this.
>
> AAA/12345/Smith J Mr
>
> What I want to do is remove the text after the last / eg.
> Smith J Mr.
>
> I have thousands of cells I need to remove this on.
>
> Please help.
>
> Thanks.
>


0
JulieD1 (2295)
7/9/2004 7:32:19 AM
Hi
you may try the following formula for this:
=LEFT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN
(A1)-LEN(SUBSTITUTE(A1,"/","")))))

>-----Original Message-----
>
>Hi,
>
>I have a spreadsheet with a list of reference numbers in 
>column A. The numbers are like this.
>
>AAA/12345/Smith J Mr
>
>What I want to do is remove the text after the last / eg. 
>Smith J Mr.
>
>I have thousands of cells I need to remove this on.
>
>Please help.
>
>Thanks.
>
>.
>
0
frank.kabel (11126)
7/9/2004 7:36:17 AM
Thanks Frank,

Your formula has partialy worked but what did'nt mention 
in my fist post and probably should have is the all the 
references are not like the examle. Here are some 
references exactly as I have them.

IEMK/CLG/108226/Grounds Cliffo
JC/cnm/101272/StockHMr
GJH/KHF/SB/78366
RJ/120849
FB/KM/42746/Croxall

Yor formula only shows so many digits eg. IEMK/CLG/10822 
appeared for the top reference of the list above.

Any Ideas?


>-----Original Message-----
>Hi Craig
>
>are all the entries the same number of characters for the 
first two
>sections,
>AAA/12345/
>BBB/67890/
>etc
>
>if so you can do this using a "helper" column - in column 
B type
>==LEFT(A1,10)
>and then double click on the fill handle (bottom right 
hand side of cell) to
>fill down
>now copy column B
>click on A1 and choose Edit / Paste Special Values
>and then you can delete column B
>
>(however, before trying this please take a backup of your 
workbook).
>
>please let us know how you go
>
>cheers
>JulieD
>
>
>"Craig" <anonymous@discussions.microsoft.com> wrote in 
message
>news:297f401c46585$4e41b000$a501280a@phx.gbl...
>>
>> Hi,
>>
>> I have a spreadsheet with a list of reference numbers in
>> column A. The numbers are like this.
>>
>> AAA/12345/Smith J Mr
>>
>> What I want to do is remove the text after the last / 
eg.
>> Smith J Mr.
>>
>> I have thousands of cells I need to remove this on.
>>
>> Please help.
>>
>> Thanks.
>>
>
>
>.
>
0
anonymous (74722)
7/9/2004 8:00:53 AM
Hi
sorry, made a mistake. Try
=LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN
(SUBSTITUTE(A1,"/","")))))

this returns
IEMK/CLG/108226/

for your first example

>-----Original Message-----
>Thanks Frank,
>
>Your formula has partialy worked but what did'nt mention 
>in my fist post and probably should have is the all the 
>references are not like the examle. Here are some 
>references exactly as I have them.
>
>IEMK/CLG/108226/Grounds Cliffo
>JC/cnm/101272/StockHMr
>GJH/KHF/SB/78366
>RJ/120849
>FB/KM/42746/Croxall
>
>Yor formula only shows so many digits eg. IEMK/CLG/10822 
>appeared for the top reference of the list above.
>
>Any Ideas?
>
>
>>-----Original Message-----
>>Hi Craig
>>
>>are all the entries the same number of characters for 
the 
>first two
>>sections,
>>AAA/12345/
>>BBB/67890/
>>etc
>>
>>if so you can do this using a "helper" column - in 
column 
>B type
>>==LEFT(A1,10)
>>and then double click on the fill handle (bottom right 
>hand side of cell) to
>>fill down
>>now copy column B
>>click on A1 and choose Edit / Paste Special Values
>>and then you can delete column B
>>
>>(however, before trying this please take a backup of 
your 
>workbook).
>>
>>please let us know how you go
>>
>>cheers
>>JulieD
>>
>>
>>"Craig" <anonymous@discussions.microsoft.com> wrote in 
>message
>>news:297f401c46585$4e41b000$a501280a@phx.gbl...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet with a list of reference numbers 
in
>>> column A. The numbers are like this.
>>>
>>> AAA/12345/Smith J Mr
>>>
>>> What I want to do is remove the text after the last / 
>eg.
>>> Smith J Mr.
>>>
>>> I have thousands of cells I need to remove this on.
>>>
>>> Please help.
>>>
>>> Thanks.
>>>
>>
>>
>>.
>>
>.
>
0
frank.kabel (11126)
7/9/2004 8:16:45 AM
Give yourself a pat on the back Frank.

Thanks so much for your help.


>-----Original Message-----
>Hi
>sorry, made a mistake. Try
>=LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN
>(SUBSTITUTE(A1,"/","")))))
>
>this returns
>IEMK/CLG/108226/
>
>for your first example
>
>>-----Original Message-----
>>Thanks Frank,
>>
>>Your formula has partialy worked but what did'nt mention 
>>in my fist post and probably should have is the all the 
>>references are not like the examle. Here are some 
>>references exactly as I have them.
>>
>>IEMK/CLG/108226/Grounds Cliffo
>>JC/cnm/101272/StockHMr
>>GJH/KHF/SB/78366
>>RJ/120849
>>FB/KM/42746/Croxall
>>
>>Yor formula only shows so many digits eg. IEMK/CLG/10822 
>>appeared for the top reference of the list above.
>>
>>Any Ideas?
>>
>>
>>>-----Original Message-----
>>>Hi Craig
>>>
>>>are all the entries the same number of characters for 
>the 
>>first two
>>>sections,
>>>AAA/12345/
>>>BBB/67890/
>>>etc
>>>
>>>if so you can do this using a "helper" column - in 
>column 
>>B type
>>>==LEFT(A1,10)
>>>and then double click on the fill handle (bottom right 
>>hand side of cell) to
>>>fill down
>>>now copy column B
>>>click on A1 and choose Edit / Paste Special Values
>>>and then you can delete column B
>>>
>>>(however, before trying this please take a backup of 
>your 
>>workbook).
>>>
>>>please let us know how you go
>>>
>>>cheers
>>>JulieD
>>>
>>>
>>>"Craig" <anonymous@discussions.microsoft.com> wrote in 
>>message
>>>news:297f401c46585$4e41b000$a501280a@phx.gbl...
>>>>
>>>> Hi,
>>>>
>>>> I have a spreadsheet with a list of reference numbers 
>in
>>>> column A. The numbers are like this.
>>>>
>>>> AAA/12345/Smith J Mr
>>>>
>>>> What I want to do is remove the text after the last / 
>>eg.
>>>> Smith J Mr.
>>>>
>>>> I have thousands of cells I need to remove this on.
>>>>
>>>> Please help.
>>>>
>>>> Thanks.
>>>>
>>>
>>>
>>>.
>>>
>>.
>>
>.
>
0
anonymous (74722)
7/9/2004 8:29:28 AM
"Frank Kabel" <frank.kabel@freenet.de> wrote...
>sorry, made a mistake. Try
>=LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)
>-LEN(SUBSTITUTE(A1,"/","")))))
>
>this returns
>IEMK/CLG/108226/
>
>for your first example
>
>>-----Original Message-----
....
>>IEMK/CLG/108226/Grounds Cliffo
>>JC/cnm/101272/StockHMr
>>GJH/KHF/SB/78366
>>RJ/120849
>>FB/KM/42746/Croxall

Yes, but your formula above returns

GJH/KHF/SB/
RJ/

for the 3rd and 4th sample lines. Maybe that's what the OP wants. If not,
and if those records should flow through as-is (as well as records
containing no slashes at all), then there's the array formula

=IF(OR(ISNUMBER(-RIGHT(A1,1)),COUNTIF(A1,"*/*")=0),A1,
LEFT(A1,MATCH(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)="/"))))


0
hrlngrv (1990)
7/9/2004 10:36:07 AM
Reply:

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Excel 2003 extract repeating cells
I have successfully sorted my data to show repeating entries in relation to two specific columns. I can't seem to figure out how to select these repeating entries (without doing it manually, of course) and putting them either into their own column(s) or an entirely different spreadsheet altogether. The goal is to save time in managing THOUSANDS of documents in this manner so that my colleagues can easily pick up repeating entries and take according action. Doing it manually is very time-inefficient. Nit Wit, You don't really describe enough of your layout or what you actually mean...

cell looses name after sorting
Can someone help me with the following problem in Excel 2000: in a table I have attached serveral cells with unique cell names, the values in these cells are used in other sheets. the problem is that when I sort the table, the cell names stay in the original rowposition; they are not sorted! while their values are. So Cell names get different values, and other calculations on my other sheets get messed up! How can I make the cell names relative instead of absolute? thankx in advance, Jim --- Message posted from http://www.ExcelForum.com/ "jimfx >" <<jimfx.109zcv@exc...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

preview text disappears when email is opened
When someone responds to an my email, I can see what they have typed - until I open the message - then only my original message is viewable. The preview pane shows new message text when email arrives, hoever when the email is opened the preview text disappears - and I just see the earlier part of the message which I had sent. If I mark it as unread it does not return to the previous state. This seems to happen on email responses that people have received and answered from a webmail service not that they downloaded and opened in outlook on their PC OL'03 XP pro sp2 -- Respectfully,...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...

removing first exchange server in org
I am ready to move the first exchange 2k3 server from my organization. I have followed the steps in article 822931 and finished them. I am just noticing that one of the public folders - entitled system configuration - is not covered in the instructions in 822931 and is set for replication between the source(server being taken out)and destination server. Should i remove the source server or does it matter. Thanks ...

Provide space in text
Where text and numbers are at different locations in different cells what formula could be used to suit this type of information. I want to be able to put a space between the main body of text on the left and give a space between the text and the start of any numbers are in the cell. Example 1: A1 = Abelia Edward Goucher2 litre Should be: Abelia Edward Goucher 2 litre Example 2: A2 = Acer campestre Nanum180stem 6-8 Should be: Acer campestre Nanum 180stem 6-8 Thankyou if can be of help. Pat If you double clcik in the cell with the data then you can move th blink line to were you wan...

Halfway through my publisher document the text starts scrambling
I am in the middle of a Publisher document that I have to get out ASAP and the words are scrmbling all of a sudden; for instance instead of the it prints out eht. HELP!! Might try an updated printer driver. What version Publisher are you using? What printer? What version Windows? Canon has issues with Publisher 98 and Windows 98 How to troubleshoot text printing (inkjet printers) in Publisher 2000 http://support.microsoft.com/default.aspx?scid=kb;en-us;198258&Product=pub PUB2000: Troubleshooting Text Printing (Laser Printers) http://support.microsoft.com/default.aspx?scid=kb;en-us;198...

Remove format link?
Is there a way to remove the format link between fields in Modifier? Help just shows how to set it and the usual stuff doesn't seem to be working. ...

Detect cell colour
I have spreadsheets where 5 cells have general titles and have different background colours. In other sheets, dozens of cells list topics and are also colour coded to show which general title they refer to. I need to tabulate information so that a row would contain three cells: the general title, a topic and data about it. Is there a command that returns the colour of the cell background, so that I could use this to select the correct title to accompany the topic? Hi, Yes there is: FontC = Worksheets("Planning").Cells(<Row>, <Column>).Font.ColorIndex Interi...

Add Remove Message Inbox Headings
Hi :) I have to do the ECDL (European Computer Driving License) Module 7 this week and I'm stuck on two points: 7.6.3.1 - Add/Remove Message Inbox Headings like: sender, subject, date, received - How do I add or remove those headings in Outlook 2007? Thanks for any help :) Giulio. ...

GENERATE a TEXT FILE Using INPUT from a Form ??
I need to generate a Text file using (partly) information entered by the user on a form. I already have the TEMPLATE for the text file......I need to generate a COPY of the file with the word entered by the user into the form INSERTED into the text file. The text file is actually code for a web page (will have an .htm extension). The file is simply an exact copy of an existing template with only ONE ITEM of the text changed to a word that is entered into a form by a user. Example: One line within the template file is: "We have several of these items available in BLUE." I have...

sort special text/numbers in format with many dots
Hi I need your help with sorting in Excel! I have mani Text fields with numbers into it. As example: 1 5.1 1.2 10.2.1 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 And it should sorted like this 1 1.2 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 5.1 10.2.1 How can I sort this like numbers? My problem is, that not all Numbers have the same format as x.x.x.x! And I can't change this Text-Fields to Numbers, because 10.6.1 looks the like 37052 :-( Any suggestions? Thx Marco Hi With your data in column A, insert a blank column at B. In B1 enter =IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".&q...

full-text indexing error
Hi Getting the following error during install at select SQL Server "Full-Text Indexing needs to be running on the specified SQL server." I've installed Index Service but cannot find any full-text indexing options anywhere? Help Matt Hi Matt, only three suggestions (haven't had this problem so far): - check the service "Microsoft Search" and start it and set type to automatic - in SQL Server Enterprise Manager go to folder "Supporting Services" (don't know the exact english name as I only have a german SQL Server here) and check the serv...

How do I format cells to a specific number of digits?
I am trying to format a column so I can enter 5 digits only. It has between 5 and 7 digit numbers in it right now and I need to delete the remaining ones. It is about 350 numbers, so it would be a lot of work to go into each cell and delete the remaning numbers. Is therea way I can format the whole column to allow 5 digits? The same happened with letters. I had codes in it that consisted of numbers and letters and the client only wants the first two letters to remain. How can it be formatted so only 2 characters are allowed to keep me from having to enter each cell separately? Hi form...

How to search a cell for specific information and to pull out that figure found !
Okay , this maybe a tough one....but I hope its solvable , basically I am uploading products to one of the more popular websites , they have various columns , one of which is comment , in this I can type in up to 1000 characters but cannot contain "<" or ">" The comment section I would like to be able to add at the end something like (min=6.75) , this would be my min selling price. There system allows you to download a re-pricing report, it shows only items where you are not the lowest price, but it's a mix of various products all have various min sell...

Listbox Help (remove selected item)
This should be easy but I can not get it to work. I have a simple listbox with Row Source Type property of value/list. It is not mult select. I am using .additem to load the listbox and am not using the itemdata property. I want to be able to select an item in the listbox, click a button on the form to get the value in column 1 of the selected item, then remove the selected item from the list box. Thanks in advance for any help "sk" <sk@discussions.microsoft.com> wrote in message news:1BB03B01-F56E-4CDE-83DF-BF6550AD8593@microsoft.com... > This should b...

how do I enter more than one line in the same Excel Cell by using.
I want to have more than one line of text in the same Excel Cell without using the wrap text format option. For example, I want to put an entire address in the Cell by typing in the name of the person, then hit enter, type the steet address, then hit enter, then type in the city, state, and zip. Use alt-enter to put in a line break. "vsimmons" wrote: > I want to have more than one line of text in the same Excel Cell without > using the wrap text format option. For example, I want to put an entire > address in the Cell by typing in the name of the person, then hit en...

Leading ' in cell values when data is Exported to Excel 2002
Hello, when exporting data to Excel, all of the cells have a leading ' . Any ideas on how to keep this from happening? I have not seen this with earlier versions of Excel and am experiencing this problem when Exporting data from WinRunner and from MS Access through VBA. Much appreciated, thanks. Hi you may try after importing the following line of code range.value=range.value "Jimmy" wrote: > Hello, when exporting data to Excel, all of the cells have a leading ' . > Any ideas on how to keep this from happening? > > I have not seen this with earlier v...

Convert Function Result To Plain Text
I want to copy the result of a concatenate function to another cell as plain text (cell contents is the textual result not the function statement). I can't seem to find the function that does that. Can you steer me to it please. There is no such a function (think it over: it's impossible), it can be done with Copy/PasteSpecial-Values! -- Regards! Stefi „"Questor"” ezt írta: > I want to copy the result of a concatenate function to another cell as plain > text (cell contents is the textual result not the function statement). I > can'...