Removing text from cells

C

Craig

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.
 
J

JulieD

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
 
F

Frank Kabel

Hi
you may try the following formula for this:
=LEFT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN
(A1)-LEN(SUBSTITUTE(A1,"/","")))))
 
C

Craig

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?
 
F

Frank Kabel

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
 
H

Harlan Grove

Frank Kabel said:
sorry, made a mistake. Try
=LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)
-LEN(SUBSTITUTE(A1,"/","")))))

this returns
IEMK/CLG/108226/

for your first example
Click to expand...

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)="/"))))
 
Top